Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amiumi66
New Contributor III

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

Re: Sorting data in semi colon format

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

7 Replies
Digvijay_Singh
Honored Contributor III

Re: Sorting data in semi colon format

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

Re: Sorting data in semi colon format

May be this?

load

Concat(item,' ') as Newfield,

Concat(cost, ',') as field2

resident Data

Group by key;

Life is so rich, and we need to respect to the life !!!
amiumi66
New Contributor III

Re: Sorting data in semi colon format

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.

amiumi66
New Contributor III

Re: Sorting data in semi colon format

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

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

amiumi66
New Contributor III

Re: Sorting data in semi colon format

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

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

Community Browser