Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
userid128223
Creator
Creator

Sorting data in semi colon format

I have 2 fields with data.

KeyItemTypeCost
123BAX12
124PPP88
123AAC43
126ITF65
124ZAY15
126QVM43
123AQV87

I want 2 strings of data seperated by semicolon that match ItemType & Cost group by Key.

Results Required:

KeyItemTypeCombinedCostCombined
123BAX,AAC,AQV12,43,87
124PPP,ZAY88,15
126ITF,QVM65,43
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

7 Replies
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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
userid128223
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator
Creator
Author

Thanks Rob

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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