If condition in Load script

[#1]

Hi,

Could you please let me know that will below script work properly based on my if condition?

Status:

LOAD

Created_at,

order_status,

Item_staus,

if(Created_at>=$v_Lasmonth and Status = ('pending','exportable','reserved','shipped'),'delivered',order_status) as order_status1

FROM C:\Users\User\Desktop\QvdName.qvd(QVD);

If not please let know how to solve this ?

Best,

Robert

[arulsettu]

try variable like this $(v_Lasmonth)

[fkeuroglian]

Or try

if(Created_at>='($v_Lasmonth)'andStatus='pending' or Status='exportable' or Status='reserved' or Status='shipped','delivered',order_status) as order_status1

[stevedark]

Hi Robert,

It will depend on the format of the Created_at field and the content of the $v_Lasmonth variable.  Assuming Created_at is actually a date and the variable has the date in YYYYMMDD format, it would be something like:

if(Created_at >= Date#('$(v_Lasmonth)', 'YYYYMMDD') and Match(lower(Status), 'exportable', 'reserved', 'shipped') > 0, 'delivered', order_status) as order_status1,

Whilst you can have date format values in variables they can be tricky to use in statements, by always converting to text when you set the variable (with a Date function) and then converting back when you use it problems can be avoided.

Hope that helps,

Steve

[#5]

Hello,

Below is my real load script: the aim of the script is that i want change the order status into delivered from last month beginning to till max of created date. But below script doesn't works.   

Sales:

LOAD id_soi,

    created_date,

     order_no,

     item_status,

     order_status,

     if(created_date >= MonthStart(Max(created_date)-30) and order_status='pending' or order_status='exportable' or order_status='reserved' or order_status='shipped','delivered',order_status) as order_status1,

    MonthStart(created_at) as mop

Resident Status;

Drop Table Status;

Create date format is : 8/1/2014 (M/D/YYYY)

Could you please advise me

Best,

Robert

[#6]

Hello all,

I have attached the sample QVW file and Data source so could you please point out me the issue

Best,

Robert

[PrashantSangle]

Hi,

Try like,

Sales:

LOAD id_soi,

    created_date,

     order_no,

     item_status,

  order_status,

     if(num(created_date) >= num(MonthStart(Max(created_date)-30)) and order_status='pending' or order_status='exportable' or order_status='reserved' or order_status='shipped','delivered',order_status) as order_status1,

    MonthStart(created_at) as mop

Resident Status;

Drop Table Status;

or

Sales:

LOAD id_soi,

    created_date,

     order_no,

     item_status,

  order_status,

     if(num(created_date) >= num(MonthStart(Max(created_date)-30)) and wildmatch(order_status,'pending','exportable','reserved','shipped'),'delivered',order_status) as order_status1,

    MonthStart(created_at) as mop

Resident Status;

Drop Table Status;

Regards

[#8]

Hi Max,

i tried your way but i am getting error and i guess my >= not working properly.

Thanks,

Robert

[stevedark]

You can't do an aggregation in line like that.

Your options are do a group by load into a temporary table and PEEK out the

max data into a variable, then use syntax like I put above.

Alternatively (and I would suggest this) use monthstart(today()). This is

simpler and it remove the risk of a rogue future posting stuffing up your

logic.

Hope that helps.

Steve

[#10]

Hi Steve,

Thanks, I don't understand exactly what your saying as i am very new Qlikview. I would be really grateful  that if you  do it in my attached sample QVW file.

Best,

Robert