Fetch Row data in to the column data with Sum and with new Table

[suhascool1]

Hi 

I have one Table1 so i need to create another Table2 from Table1 by using script.

Table1 which is fixed and coming from upload excel data source.

but i need to another table (TABLE2)on basis of Table1

in Table Storage location showing in the column and quantity also sum.

Table1

Table1
MaterialCode	MaterialName	Storage Location	BATCH	Quantity
80.9832	Mango	SL11	98394	2	KG
90.8345	Grapes	SL12	45933	4	KG
57.8483	Banana	SL13	84832	3	KG
69.3452	Apple	SL11	23454	10	KG
80.9832	Mango	SL11	23245	20	KG
90.8345	Grapes	SL12	56544	3	KG
57.8483	Banana	SL13	34567	5	KG
69.3452	Apple	SL12	33456	6	KG

Table 2

Table2
Material	MaterialName	SL11	SL12	SL13
80.9832	Mango	20
90.8345	Grapes		7
57.8483	Banana			8
69.3452	Apple	10	6

[suhascool1]

Table2
Material	MaterialName	SL11	SL12	SL13
80.9832	Mango	22
90.8345	Grapes		7
57.8483	Banana			8
69.3452	Apple	10	6

[theoat]

You can do this in the script. After importing your data, enter the code on the right.
TABLE2:
Load Distinct
MaterialCode,
[MaterialName] as [MaterialName],
if([Storage Location]='SL11',[Quantity],'') as SL11,
if([Storage Location]='SL12',[Quantity],'') as SL12,
if([Storage Location]='SL13',[Quantity],'') as SL13
RESIDENT TABLE1;

More informations in this pictures.

Kind regards,
Théo ATRAGIE.

[marksouzacosta]

Hi @suhascool1 ,

The easiest way is to add a Pivot Table chart. The chart will look like this:

Is that what are you looking for?

[suhascool1]

Hi 

Thank you for the help,

but is it possible to show combine material with one row addition of sum. 

Material	MaterialName	SL11	SL12	SL13
80.9832	Mango	22
90.8345	Grapes		7
57.8483	Banana			8
69.3452	Apple	10	6

[theoat · Accepted solution]

Try this :

TABLE1:
LOAD * INLINE [
MaterialCode, MaterialName, Storage Location, BATCH, Quantity
80.9832, Mangue, SL11, 98394, 2, KG
90.8345, Raisins, SL12, 45933, 4, KG
57.8483, Banane, SL13, 84832, 3, KG
69.3452, Pomme, SL11, 23454, 10, KG
80.9832, Mangue, SL11, 23245, 20, KG
90.8345, Raisins, SL12, 56544, 3, KG
57.8483, Banane, SL13, 34567, 5, KG
69.3452, Pomme, SL12, 33456, 6, KG
];

TABLE2:
Load Distinct
MaterialCode,
if([Storage Location]='SL11',[Quantity],0) as SL11,
if([Storage Location]='SL12',[Quantity],0) as SL12,
if([Storage Location]='SL13',[Quantity],0) as SL13
RESIDENT TABLE1;

TABLE3:
Load Distinct
MaterialCode,
MaterialName as MaterialName2
RESIDENT TABLE1;

TABLE3a:
Load Distinct
MaterialCode,
sum([SL11]) as SL11
RESIDENT TABLE2
Group by MaterialCode;

TABLE3b:
Load Distinct
MaterialCode,
sum([SL12]) as SL12
RESIDENT TABLE2
Group by MaterialCode;

TABLE3c:
Load Distinct
MaterialCode,
sum([SL13]) as SL13
RESIDENT TABLE2
Group by MaterialCode;

left join(TABLE3)
load *
resident TABLE3a;

left join(TABLE3)
load *
resident TABLE3b;

left join(TABLE3)
load *
resident TABLE3c;

drop tables TABLE3a,TABLE3b,TABLE3c,TABLE2;

Kind Regards,
Théo ATRAGIE.

[suhascool1 · Accepted solution]

Hi Theot

Thank you very much!!!  👍

Best Regards

Suhas