Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
any help?
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;
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
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
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;