Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 fields with data.
Key | ItemType | Cost |
---|---|---|
123 | BAX | 12 |
124 | PPP | 88 |
123 | AAC | 43 |
126 | ITF | 65 |
124 | ZAY | 15 |
126 | QVM | 43 |
123 | AQV | 87 |
I want 2 strings of data seperated by semicolon that match ItemType & Cost group by Key.
Results Required:
Key | ItemTypeCombined | CostCombined |
---|---|---|
123 | BAX,AAC,AQV | 12,43,87 |
124 | PPP,ZAY | 88,15 |
126 | ITF,QVM | 65,43 |
You can do the concat and preserve the order by using RecNo() as the sort weight parameter:
LOAD
Key,
concat(ItemType,',',RecNo()) as ItemTypeCombined,
concat(Cost,',',RecNo()) as CostCombined
FROM
[https://community.qlik.com/thread/258599]
(html, codepage is 1252, embedded labels, table is @1)
Group by Key;
-Rob
Use Key as Dimension and Concat(Itemtype,';') and Concat(Cost,';') as expressions.
May be this?
load
Concat(item,' ') as Newfield,
Concat(cost, ',') as field2
resident Data
Group by key;
Anil doing concat as you suggested will loose it orignal sort order. It will do numbers from low to high and text field Alphabetical order. That's not what i want.
It will loose the sort order i want. Your way would produce.
123 AAC,AQV,BAX
123 12,43,87 (if number fell in differently it would sort by low to high)
i want to preserve the sort order as per data.
You can do the concat and preserve the order by using RecNo() as the sort weight parameter:
LOAD
Key,
concat(ItemType,',',RecNo()) as ItemTypeCombined,
concat(Cost,',',RecNo()) as CostCombined
FROM
[https://community.qlik.com/thread/258599]
(html, codepage is 1252, embedded labels, table is @1)
Group by Key;
-Rob
Thanks Rob
Any other method of doing this on the fly in the chart instead of within script?
Yes. Save the RecNo() in a field and then use that in the chart expression.
LOAD
RecNo() as RecId,
Key,
ItemType,
Cost
FROM
[https://community.qlik.com/thread/258599]
(html, codepage is 1252, embedded labels, table is @1);
Then chart Dim "Key" and expressions:
concat(ItemType,',',RecId)
concat(Cost,',',RecId)
-Rob