Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Creating new variable in script

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?

Tags (1)
1 Solution

Accepted Solutions

Re: Creating new variable in script

Hi Dominykas,

another one preserving the Card Type dimension table:

QlikCommunity_Thread_118911_Pic1.JPG.jpg

QlikCommunity_Thread_118911_Pic2.JPG.jpg

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

View solution in original post

5 Replies
Highlighted
Not applicable

Re: Creating new variable in script

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

Highlighted

Re: Creating new variable in script

Hi,

may be like this.

Regards

ASHFAQ

Highlighted

Re: Creating new variable in script

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

Highlighted
MVP
MVP

Re: Creating new variable in script

1.png

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

Hi Dominykas,

another one preserving the Card Type dimension table:

QlikCommunity_Thread_118911_Pic1.JPG.jpg

QlikCommunity_Thread_118911_Pic2.JPG.jpg

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

View solution in original post