Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, guys,
I`m still quite new to qlickview and English is not my mother tong sometimes it`s hard to express myself correctly.
I have 2 tables
The 1st one contains Purchase number Payment type and purchase value
Purchase_nr Payment_type Purchase_ value
1 Cash 300
2 Credit card 250
3 Credit card 400
4 Cash 450
5 Credit card 100
6 Cash 150
Second contains Purchase number and Credit card type
Purchase_nr Card_type
2 Visa Classic
4 Visa Electron
5 American express
As you can see from the data some data is missing and not always correct
What I want to do is to create new variable (or expression not sure how to call it) that would look like that
If (Payment_type=’Cash’ and Card_type =’’, ‘Cash’, If (Card_type <>’’, Card_type, If(Payment_type=’Credit card’,’Credit card unknown’ as Payment_cat
I want to place it into load statement but some errors arise any suggestions?
Hi Dominykas,
another one preserving the Card Type dimension table:
tabCardType:
LOAD * INLINE [
Purchase_nr, Card_type
2, Visa Classic
4, Visa Electron
5, American Express
];
tabPurchases:
LOAD Purchase_nr, Payment_type, Purchase_value,
If(Payment_type='Cash' and IsNull(CT), 'Cash', If(IsNull(CT), If(Payment_type='Credit card','Credit card unknown'), CT)) as Payment_cat;
LOAD *, Lookup('Card_type', 'Purchase_nr', Purchase_nr, 'tabCardType') as CT
INLINE [
Purchase_nr, Payment_type, Purchase_value
1, Cash, 300
2, Credit card, 250
3, Credit card, 400
4, Cash, 450
5, Credit card, 100
6, Cash, 150
];
hope this helps
regards
Marco
Hi,
try this script:
pays_tmp:
LOAD * INLINE [
Purchase_nr,Payment_type,Purchase_ value
1,Cash,300
2,Credit card,250
3,Credit card,400
4,Cash,450
5,Credit card,100
6,Cash,150
];
left join (pays_tmp)
LOAD * INLINE [
Purchase_nr, Card_type
2,Visa Classic
4,Visa Electron
5,American express
];
pays:
load *,
If (Payment_type='Cash' and isnull(Card_type), 'Cash', If (not isnull(Card_type), Card_type, If(Payment_type='Credit card','Credit card unknown'))) as Payment_cat
Resident pays_tmp;
drop table pays_tmp
Hi,
may be like this.
Regards
ASHFAQ
You can try this way by the below load script
Tmp:
LOAD * INLINE [
Purchase_nr,Payment_type,Purchase_ value
1,Cash,300
2,Credit card,250
3,Credit card,400
4,Cash,450
5,Credit card,100
6,Cash,150
];
Left Join (Tmp)
LOAD * INLINE [
Purchase_nr, Card_type
2,Visa Classic
4,Visa Electron
5,American express
];
FinalTable:
load *,
If(Payment_type='Cash' and IsNull(Card_type) = -1 , 'Cash', If (not IsNull(Card_type) = -1, Card_type, If(Payment_type='Credit card','Credit card unknown'))) as Payment_cat
Resident Tmp;
Drop table Tmp
MapTable:
mapping load * inline
[
Purchase_nr,Card_type
2,Visa Classic
4,Visa Electron
5,American Express
];
Table:
load *,
If(Payment_type='Cash' and ApplyMap('MapTable', Purchase_nr, 'Missing')='Missing', 'Cash',
If(ApplyMap('MapTable', Purchase_nr, 'Missing')<>'Missing', ApplyMap('MapTable', Purchase_nr),
If(Payment_type='Credit card','Credit card unknown'))) as Payment_cat;
load * Inline
[
Purchase_nr,Payment_type,Purchase_ value
1,Cash,300
2,Credit card,250
3,Credit card,400
4,Cash,450
5,Credit card,100
6,Cash,150
];
Hi Dominykas,
another one preserving the Card Type dimension table:
tabCardType:
LOAD * INLINE [
Purchase_nr, Card_type
2, Visa Classic
4, Visa Electron
5, American Express
];
tabPurchases:
LOAD Purchase_nr, Payment_type, Purchase_value,
If(Payment_type='Cash' and IsNull(CT), 'Cash', If(IsNull(CT), If(Payment_type='Credit card','Credit card unknown'), CT)) as Payment_cat;
LOAD *, Lookup('Card_type', 'Purchase_nr', Purchase_nr, 'tabCardType') as CT
INLINE [
Purchase_nr, Payment_type, Purchase_value
1, Cash, 300
2, Credit card, 250
3, Credit card, 400
4, Cash, 450
5, Credit card, 100
6, Cash, 150
];
hope this helps
regards
Marco