topic Re: Nested if Condition in Script in QlikView

Nested if Condition in Script

Tue, 25 Aug 2015 12:39:13 GMT

Hi all,

I am new to qlikview. I used nested if conditions in my script. But some of the conditions doesnt work. (Test1 and Test2, mentioned in the script)

Sample:

LOAD Product,

if((Product='Two Wheelers' or Product='Three Wheelers') and Level='Level1','Test3',

if(Product='Two Wheelers','Test1',

if(Product='Three Wheelers','Test2'))) as New_Skill,

Level,

Value

FROM

(ooxml, embedded labels, table is Sheet2);

I know the first condition satisfies , so it not performing the other conditions. But i need Test1 and Test2 in the New_Skill Field.

Please find the attachment and help me sort out this issue.

Regards,

Siva

Re: Nested if Condition in Script

stigchel — Tue, 25 Aug 2015 15:35:40 GMT

If I understand you correctly and you need more values per row for the New_Skill field you can use a crosstable load, something like (Also see attached)

Sample:

LOAD Product,

Level,

Value

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet2);

CrossTable (New_Skill,Product,1) load

RowNo() As Id,

if((Product='Two Wheelers' or Product='Three Wheelers') and Level='Level1',Product) as Test3,

if(Product='Two Wheelers',Product) as Test1,

if(Product='Three Wheelers',Product) as Test2

resident Sample;

Re: Nested if Condition in Script

Qrishna — Tue, 25 Aug 2015 15:36:24 GMT

IS this what you are expecting?

Please see attached.

Re: Nested if Condition in Script

Oleg_Troyansky — Tue, 25 Aug 2015 15:43:56 GMT

Based on your condition and your data, the second and the third condition will never get processed. In order to see "Test1" and "Test2", you should have some Two Wheelers and Three Wheelers with Level other than Level1.

Re: Nested if Condition in Script

Tue, 25 Aug 2015 16:26:22 GMT

Hi,

Sample:

LOAD Product,

if(Product='Two Wheelers' and Level='Level1','Test3',

if(Product='Three Wheelers' and Level='Level1','Test3',

if(Product='Two Wheelers','Test1',

if(Product='Three Wheelers','Test2')))) as New_Skill,

Level,

Value

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet2);

Try the above code.

Please see attached qvw also. To check I have added two rows in your excel.

Re: Nested if Condition in Script

pratap6699 — Wed, 26 Aug 2015 04:17:10 GMT

product_tbl:

LOAD Product,

if(Product='Two Wheelers' and Level='Level1','Test3',

if(Product='Three Wheelers' and Level='Level1','Test3',

if(Product='Two Wheelers','Test1',

if(Product='Three Wheelers','Test2')))) as New_Skill,

Level,

Value

FROM

Sample.xlsx

your expecting output is like this??.

Product	New_Skill	Level	Value
Four Wjeelers		Level2	300
JCB		Level3	400
Three Wheelers	Test2	Level3	250
Three Wheelers	Test3	Level1	200
Two Wheelers	Test1	Level2	150
Two Wheelers	Test3	Level1	100

Re: Nested if Condition in Script

jonathandienst — Wed, 26 Aug 2015 05:49:00 GMT

if(Product='Two Wheelers' and Level='Level1','Test3',

if(Product='Three Wheelers' and Level='Level1','Test3',

if(Product='Two Wheelers','Test1',

if(Product='Three Wheelers','Test2')))) as New_Skill,

Logically identical to the original post...

See Oleg Troyansky's post

Re: Nested if Condition in Script

Wed, 26 Aug 2015 05:57:17 GMT

Thanks for your reply. Actually the Linking field here is product. But in my case both tables are linked by two fields.

For Example : ProductID&'-'&CategoryID.

I am not able to bring this key field in the crosstable . I hope you can understand my response.

Regards,

Siva

Re: Nested if Condition in Script

jonathandienst — Wed, 26 Aug 2015 06:01:37 GMT

There's nothing wrong with your script. You just have no data in your source sample that satisfies the conditions for Test2 and Test3.

Re: Nested if Condition in Script

Wed, 26 Aug 2015 06:33:27 GMT

yes i know there is no data fro Test 2 and Test 3. Actually the Cross table concept is correct. It works. But the problem is , not able to bring the key fields in the cross table.

Regards,

Siva

Re: Nested if Condition in Script

jonathandienst — Wed, 26 Aug 2015 06:35:48 GMT

I see nothing in your original question which implies a cross table load is required.

Re: Nested if Condition in Script

sasiparupudi1 — Wed, 26 Aug 2015 06:39:57 GMT

Please provide your full data document so that it would be easier to provide a solution otherwise it will be a guess work

Re: Nested if Condition in Script

Wed, 26 Aug 2015 06:47:28 GMT

Hi Jonathan,

How you will get the attached word file output with my Original script. It is not possible.

Regards,

Siva

Re: Nested if Condition in Script

stigchel — Wed, 26 Aug 2015 08:36:54 GMT

Hi Siva,

The third parameter of the crosstable load defines how many fields from the top are loaded as qualifier fields. So in this case instead of the rowno, you can use your key (and name it to the other linking field)

CrossTable (New_Skill,Product,1) load

ProductID&'-'&CategoryID As Id,

if((Product='Two Wheelers' or Product='Three Wheelers') and Level='Level1',Product) as Test3,

if(Product='Two Wheelers',Product) as Test1,

if(Product='Three Wheelers',Product) as Test2

resident Sample;