Nested IF by variables with IF in Load statement

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer]

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