topic Re: Sorting data in semi colon format in QlikView

Sorting data in semi colon format

userid128223 — Thu, 27 Apr 2017 21:20:56 GMT

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

Re: Sorting data in semi colon format

Digvijay_Singh — Thu, 27 Apr 2017 21:23:33 GMT

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

Re: Sorting data in semi colon format

Anil_Babu_Samineni — Thu, 27 Apr 2017 21:29:12 GMT

May be this?

load

Concat(item,' ') as Newfield,

Concat(cost, ',') as field2

resident Data

Group by key;

Re: Sorting data in semi colon format

userid128223 — Thu, 27 Apr 2017 21:46:39 GMT

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.

Re: Sorting data in semi colon format

userid128223 — Thu, 27 Apr 2017 21:48:59 GMT

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.

Re: Sorting data in semi colon format

rwunderlich — Thu, 27 Apr 2017 22:14:49 GMT

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

Re: Sorting data in semi colon format

userid128223 — Thu, 27 Apr 2017 22:42:52 GMT

Thanks Rob

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

Re: Sorting data in semi colon format

rwunderlich — Thu, 27 Apr 2017 23:17:44 GMT

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