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

[stevedark]

Afraid that is a bit difficult whilst stood on an over crowded train!

Simply replace the text in your code:

max(created_date)

With:

today()

That should then work. This presumes that created_date is a timestamp and

the max is generally going to be today's date.

Steve

[#3]

Hi Steve,

I have tried something like below as you said to load temp table and store into the variable but even it doesn't work.

Date:

LOAD

   MonthStart(Max(created_date)-30) as LastMonth

RESIDENT Status;

LET v_LastMonth = num(peek('LastMonth',0,'Date'));

DROP TABLE Date;

Sales:

LOAD id_soi,

    created_date,

     order_no,

    item_status,

     order_status,

     if(num(created_date) >= '($v_Lastmonth)' and wildmatch(order_status,'pending','exportable','reserved','shipped'),'delivered',order_status) as order_status1,

    MonthStart(created_at) as mop

Resident Status;

Best,

Robert

[#4]

Dear Qlikview Guru,

Since yesterday, I was trying to find the solution for my post but i could't find till now.

I would be really appreciate if anyone give me exact solution for my attached QVW file.

Best,

Robert

[CELAMBARASAN]

May be the below script will help you

Source:

LOAD Date(created_at,'YYYY-MM-DD')as created_at,

     mop,

     order_status

FROM

[Order_Status.xlsx]

(ooxml, embedded labels, table is Sheet1);

//D:\QlikView\QV Community Discussion\

Date:

LOAD

  Max(created_at) AS LastMonth

Resident Source;

LET v_Lastmonth = MonthStart(Peek('LastMonth',0, 'Date'), -1);

DROP Table Date;

Status:

LOAD

created_at,

mop,

order_status,

if(created_at >= $(v_Lastmonth) and MixMatch(order_status, 'exportable','delivered'),order_status) as order_status1

Resident Source;

DROP Table Source;

[#6]

Hi Celambu,

yours script doesn't work ... please advise any other way to fix this issue.

Best,

Robert.

[PrashantSangle · Accepted solution]

Hi,

MOdify your script to and Reload it

Source:

LOAD created_at,
  NUM(created_at) AS NUM_CREATED_DATE,
  Date(floor(created_at),'YYYY-MM-DD') AS NEW_CREATED_DATE,
    mop,
    order_status
FROM

[..\Desktop\Order_Status.xlsx]

(ooxml, embedded labels, table is Sheet1);

Status:
LOAD
NEW_CREATED_DATE,
mop,
order_status,
if((NUM_CREATED_DATE >= MonthStart(Today()-30)) and order_status ='exportable','delivered',order_status) as order_status1
Resident Source;

DROP Table Source;




Find attached file for your reference

[CELAMBARASAN]

Could you please describe your requirement, so that I can check what is the issue in the script?

[#9]

Awesome ... Many Thanks max.

See you with next challenging question.

[#10]

Hi Celambarasan,

Thanks.

I imported my transnational  data into qlikview and used to reload every day. the aim is that i want to update my order status into delivered if  order_status = ('pending','exportable','reserved','shipped') and only last month of data from today.

Max given me the answer that whats exactly i want.  if you any other answer for my question you could send it to me as well since i want to learn QlikView soon.

Best,

Robert

[stevedark]

To get the code you have posted above to work you will need to change the following...

LET v_LastMonth = num(peek('LastMonth',0,'Date'));

Should read:

LET v_LastMonth = Date(peek('LastMonth',0,'Date'), 'YYYYMMDD');

And then:

     if(num(created_date) >= '($v_Lastmonth)' and

Should read:

     if(created_date >= Date#('$(v_Lastmonth)', 'YYYYMMDD') and

That should then work.

This converts the date into a string that can be human read in the debugger or variable overview when the peek is done.  The date string is then converted back to a date when it is compared.

To do it using the num function, you should use num# instead of num, and you should not use the quote marks around the variable when you do the compare.  You also have a bracket out of place on the variable read.

I would strongly recommend going via a string though - if you are going the route of a temporary table - as it is more robust.  If a null date is encountered the code will still function, but if a null is encountered when using numbers to compare it can crash the load out.

Steve