Create table for report (new sheet)?

[fairymemay (noona)]

I have input as below.

I want output as below

My file as attached.
pivot data.xlsx (21.5 KB)

Please guide me about it.

[Yoichi]

Hi,

Can you try the following sample?

Sample20220331-5.zip (37.4 KB)

Note: I removed some rows which has no numeric data.

Regards,

[fairymemay (noona)]

@Yoichi If count = 0 , I want write “-” as yellow highlight.

Please guide me for edit code.

[Yoichi]

Hi,

Can you try to add the following Assign activity?

dr.ItemArray = Enumerable.Range(0,arrCol.Length+1).Select(Function(i) "-").ToArray

Regards,

[fairymemay (noona)]

@Yoichi I have a question.

If In activity assign , I don’t want use number but I want use column Name (Type , จำนวน)

How to edit your code?

[Yoichi]

Hi,

Can you try to modify as the following?

r(0) → r(“AGNAME”)
r(1) → r(“Type”)
r2(2) → r2(“จำนวน”)

Regards,

[fairymemay (noona)]

@Yoichi Now I use file as attached.

pivot data.xlsx (60.9 KB)

Main.xaml (17.1 KB)

But show error as below.

Please guide me for solve it.

[Yoichi]

Hi,

Probably because there is blank cell. So can yo try the following expression? (Please modify column index to column name)

dict = dt.AsEnumerable.GroupBy(Function(r) r(0).ToString ).OrderBy(Function(g) g.Key).ToDictionary(Function(g) g.Key,Function(g) g.GroupBy(Function(r) r(1).ToString).ToDictionary(Function(g2) g2.Key, Function(g2) g2.Sum(Function(r2) if(Int32.TryParse(r2(2).ToString,New Int32) ,Int32.Parse(r2(2).ToString),0))))

Regards,

[fairymemay (noona)]

@Yoichi After edit follow your suggest.

It error in next step as below.

[Yoichi]

Hi,

Can you share content of $exceptionDetails at LocalsPanel in the above?

Regards,

[fairymemay (noona)]

@Yoichi $exceptionDetails as below.

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.ArgumentException: Column ‘’ does not belong to table TableName.
at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.set_Item(String columnName,
Object value)
— End of inner exception stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target,
Object arguments,
Signature sig,
Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj,
Object parameters,
Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj,
BindingFlags invokeAttr,
Binder binder,
Object parameters,
CultureInfo culture)
at System.Activities.ExpressionUtilities.IndexerLocationFactory1.IndexerLocation.set_Value(T value) at System.Activities.Location1.ReferenceLocation.set_Value(T value)
at System.Activities.ActivityContext.SetValueCore[T](LocationReference locationReference,
T value)
at System.Activities.ActivityContext.SetValue[T](LocationReference locationReference,
T value)
at System.Activities.Argument.Set(ActivityContext context,
Object value)
at System.Activities.Statements.Assign.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance,
ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)

[Yoichi]

Hi,

It’s because there is blank cell in Type column.
Do you need rows which has blank cell in Type column?
If you don’t need them, please filter out them in advance using FilterDatatable etc.

Regards,

[fairymemay (noona)]

@Yoichi Do you need rows which has blank cell in Type column?
Yes, I need because some row = blank.

[Yoichi]

Hi,

Can you share expected result regarding these blank?
It seems not only Type but also value are both blank, and these value are always “-” , as a result, i think.

Regards,

[fairymemay (noona)]

@Yoichi I solve solution by use filter that your suggest.

Thank you