topic Re: If condition in Load script in QlikView

If condition in Load script

Wed, 15 Oct 2014 11:17:45 GMT

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

Re: If condition in Load script

arulsettu — Wed, 15 Oct 2014 11:26:44 GMT

try variable like this $(v_Lasmonth)

Re: If condition in Load script

fkeuroglian — Wed, 15 Oct 2014 22:29:11 GMT

Or try

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

Re: If condition in Load script

stevedark — Wed, 15 Oct 2014 22:35:12 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 03:47:22 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 05:40:11 GMT

Hello all,

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

Best,

Robert

Re: If condition in Load script

PrashantSangle — Thu, 16 Oct 2014 05:45:01 GMT

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;

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

Re: If condition in Load script

Thu, 16 Oct 2014 05:57:53 GMT

Hi Max,

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

Thanks,

Robert

Re: If condition in Load script

stevedark — Thu, 16 Oct 2014 06:30:00 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 07:38:35 GMT

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

Re: If condition in Load script

stevedark — Thu, 16 Oct 2014 07:51:01 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 09:15:50 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 09:19:12 GMT

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

Re: If condition in Load script

CELAMBARASAN — Thu, 16 Oct 2014 09:29:25 GMT

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;

Re: If condition in Load script

Thu, 16 Oct 2014 09:46:38 GMT

Hi Celambu,

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

Best,

Robert.

Re: Re: If condition in Load script

PrashantSangle — Thu, 16 Oct 2014 10:43:48 GMT

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

Re: If condition in Load script

CELAMBARASAN — Thu, 16 Oct 2014 11:13:07 GMT

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

Re: If condition in Load script

Thu, 16 Oct 2014 11:13:36 GMT

Awesome ... Many Thanks max.

See you with next challenging question.

Re: If condition in Load script

Thu, 16 Oct 2014 11:19:55 GMT

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

Re: If condition in Load script

stevedark — Thu, 16 Oct 2014 11:38:47 GMT

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