topic Re: Creating new variable in script in QlikView

Creating new variable in script

Mon, 19 May 2014 12:27:47 GMT

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 1^st 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?

Re: Creating new variable in script

Mon, 19 May 2014 13:00:57 GMT

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

Re: Creating new variable in script

ashfaq_haseeb — Mon, 19 May 2014 13:14:08 GMT

Hi,

may be like this.

Regards

ASHFAQ

Re: Creating new variable in script

its_anandrjs — Mon, 19 May 2014 13:32:48 GMT

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

Re: Creating new variable in script

maxgro — Mon, 19 May 2014 20:52:05 GMT

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

];

Re: Creating new variable in script

MarcoWedel — Mon, 19 May 2014 22:20:47 GMT

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