Sorting data in semi colon format

[userid128223]

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

[Digvijay_Singh]

Use Key as Dimension and Concat(Itemtype,';') and Concat(Cost,';') as expressions.

[Anil_Babu_Samineni]

May be this?

load

Concat(item,' ') as Newfield,

Concat(cost, ',') as field2

resident Data

Group by key;

[userid128223]

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.

[userid128223]

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.

[rwunderlich · Accepted solution]

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

http://masterssummit.com

http://qlikviewcookbook.com

[userid128223]

Thanks Rob

Any other method of doing this on the fly in the chart instead of within script?

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com