Finding Values from Type of Code

[reshmakala]

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.

[sunny_talwar · Accepted solution]

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;

[JoaquinLazaro]

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

[reshmakala]

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

[florentina_doga]

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;