Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
Thank you so much Sunny! You have been very helpful.
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;