topic Re: Finding Values from Type of Code in QlikView

Finding Values from Type of Code

reshmakala — Tue, 28 Jun 2016 13:49:37 GMT

Hi all,

I have records where, for a single order, the payment is done by credit card and by gift card by splitting the amount. I have my data as below.

Order Number	Payment_cd	Identifier
223436AGR435	246853	5CR
223436AGR435	574245	465
223436AGR435	458168	8PG

Here for a single order 223436AGR435, three types of Payments were used. If the identifier is alphanumeric, the Payment_cd indicates a gift card.

If the Identifier is numeric, the Payment_cd indicates a credit card.

I need another two columns where the primary_payment is credit card and secondary_payment is giftcard. I need the above table to be shown as below:

Order Number	Primary_payment	Secondary Payment
223436AGR435	574245	246853
223436AGR435	574245	458168

I need this in load script. Please help me.

Re: Finding Values from Type of Code

sunny_talwar — Tue, 28 Jun 2016 13:53:32 GMT

May be this:

Table:

LOAD [Order Number],

Payment_cd,

Identifier

FROM

[https://community.qlik.com/thread/222851]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD [Order Number],

Payment_cd as [Primary Payment]

Resident Table

Where IsNum(Identifier);

Left Join (FinalTable)

LOAD [Order Number],

Payment_cd as [Secondary Payment]

Resident Table

Where IsText(Identifier);

DROP Table Table;

Re: Finding Values from Type of Code

Joaquin_Lazaro — Tue, 28 Jun 2016 13:58:08 GMT

Hello:

LOAD OrderNumber,

Payment_CD,

If (IsNum(Identifier), Identifier, Null()) as CreditCard,

If (IsText(Identifier), Identifier, Null()) as GiftCard

Follow the script with a join with the second table

Hope this help you

Joaquín

Re: Finding Values from Type of Code

reshmakala — Tue, 28 Jun 2016 13:59:32 GMT

Thank you so much Sunny! You have been very helpful.

Re: Finding Values from Type of Code

florentina_doga — Tue, 28 Jun 2016 14:01:49 GMT

hope this help

aa:

load *,

num(Identifier) as test;

load * inline [Order Number, Payment_cd, Identifier

223436AGR435, 246853, 5CR

223436AGR435, 574245, 465

223436AGR435, 458168, 8PG];

bb:

load distinct

[Order Number],

if(len(test)<>0,Payment_cd) as primary_payment

resident aa;

left join

load distinct

[Order Number],

if(len(test)=0,Payment_cd) as secondary_payment

resident aa;

NoConcatenate

cc:

load distinct *

Resident bb where len(primary_payment)<>0 and len(secondary_payment)<>0;

drop table bb;