Grouping of Field values

[#1]

Hi All,

I have data like below

NO	CAT	Amount
1	AB	100
1	AB	200
1	BC	110
2	CD	130
2	CD	120
2	CD	110

Need output like below

	AB	BC	CD
1	300	110	0
2	0	0	360

Note:

I need to achieve it from script

[#2]

any help?

[Kushal_Chawda · Accepted solution]

see this

Data:
Generic LOAD NO,CAT,sum(Amount) as Amount
Group by NO,CAT;
LOAD * Inline [
NO  CAT  Amount
1    AB  100
1    AB  200
1    BC  110
2    CD  130
2    CD  120
2    CD  110 ] (delimiter is '') ;

for i=0 to NoOfTables()

Tables:
LOAD *
WHERE TableName<>'Tables';
LOAD TableName('$(i)') as TableName
AutoGenerate 1;

NEXT i

Final:
LOAD * Inline [
Junk ];

for i=1 to FieldValueCount('TableName');

let vTableName = FieldValue('TableName',$(i));

Join(Final)
LOAD *
Resident $(vTableName);

DROP Table $(vTableName);

NEXT i

DROP Field Junk;
DROP Table Tables;

[ramoncova06]

whenever you open the file click on the enable transformations step and select the rotate tab, and pick which ever option is best suited to cover your needs

[Kushal_Chawda]

can you show me example with this data? I had tried that but was not working. I just think of generic load seeing the data

[sunny_talwar]

Another possibility:

Table:

LOAD NO,

  CAT,

  Sum(Amount) as Amount

Group By NO, CAT;

LOAD * Inline [

NO, CAT, Amount

1, AB, 100

1, AB, 200

1, BC, 110

2, CD, 130

2, CD, 120

2, CD, 110

];

FinalTable:

LOAD Distinct NO

Resident Table;

For i = 1 to FieldValueCount('CAT')

  LET vField = FieldValue('CAT', $(i));

  Join(FinalTable)

  LOAD NO,

  Amount as $(vField)

  Resident Table

  Where CAT = '$(vField)';

NEXT

DROP Table Table;