Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping of Field values

Hi All,

I have data like below

 

NOCATAmount
1AB100
1AB200
1BC110
2CD130
2CD120
2CD110

Need output like below

 

         AB          BC          CD
13001100
200360

Note:

I need to achieve it from script

1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

5 Replies
Not applicable
Author

any help?

Kushal_Chawda

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;

View solution in original post

ramoncova06
Specialist III
Specialist III

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;


Capture.PNG