topic Re: Nested IF by variables with IF in Load statement in QlikView

Nested IF by variables with IF in Load statement

Fri, 05 Aug 2016 12:00:25 GMT

Hello folks,

I am loading some fields along with the following.

LOAD

product,

category,

delivery_date,

if ($(vIsLAConf) > $(vIsABConfLatest),0) as Delayed,

if ($(vIsLAConf) < $(vIsABConfLatest),0) as OnTime

FROM xyz.qvd (qvd);

execution log for the if statement:

-------------------------------------------------

0030 if (if(category = 'LA' , delivery_date) > if(category = 'AB' , delivery_date),0) as Delayed,

0031 if (if(category = 'LA' , delivery_date) < if(category = 'AB' , delivery_date),0) as OnTime

The thing here is , i want to display the count of 'Delayed' and 'OnTime' for every product. Please help me to correct this nested IF formed above so that i will get the correct count.

Attached snap is sample data from xyz.qvd

Cheers,

James

Re: Nested IF by variables with IF in Load statement

marcus_sommer — Fri, 05 Aug 2016 13:49:25 GMT

You approach couldn't work because you could in this way only check values which are within the same record. This meant you will need to bring these different records into a single record with a logic like:

load * FROM xyz.qvd (qvd);

left join

load product, category, delivery_date as NewDate FROM xyz.qvd (qvd) where category = 'AB';

You might need some further adjustements to the where clauses to determine which records should be merged and afterwards you could load these new created table and make there your comparisation.

- Marcus

Re: Nested IF by variables with IF in Load statement

Mon, 08 Aug 2016 08:49:48 GMT

Thanks Marcus. Now I did this.

LOAD

product,

category,

delivery_date as LADate

From xyz.qvd (qvd)

Where category = 'LA' ;

Left Join

LOAD

product,

category,

delivery_date as ABDate

From xyz.qvd (qvd)

Where sap_confirm_category = 'AB' ;

Please refer the sample table format and column names into which i have to load values. It's actually count values by month. I have to do 4 things here.

1.) Count total order lines (with a condition acct_assignment_cat = '' OR ' ' )

2.) Delayed order lines ( with the condition if( LADate > ABDate , count (product))

3.) On time order lines ( with the condition if( ABDate> LADate , count (product))

4.) COT % ( On time order lines / total order lines * 100 )

How can i achieve all ? why because I am confused to put condition in load statement for loading dates and to put conditions to show the count for total , delayed , on time in the table. please clarify.

-James

Re: Nested IF by variables with IF in Load statement

marcus_sommer — Mon, 08 Aug 2016 10:35:26 GMT

I would create a flag in a following load like:

temp:

LOAD product, category, delivery_date as LADate

From xyz.qvd (qvd) Where category = 'LA' ;

Left Join (temp)

LOAD product, category, delivery_date as ABDate

From xyz.qvd (qvd) Where sap_confirm_category = 'AB' ;

final:

LOAD *, if(LADate > ABDate, 1, 2) as Flag resident temp:

drop tables temp;

and within a chart you could calculate:

count({< Flag = {1}>} product)

count({< Flag = {2}>} product)

count({< Flag = {1}>} product) / count({< Flag = {2}>} product)

- Marcus