topic Re: if statement when loading data from db in QlikView

if statement when loading data from db

Wed, 18 Mar 2015 12:19:26 GMT

Hello all,

I'm new here and, already asking questions!

I have a requirement to create an incremental load script from a table which has about 6,5kk entries, which I kind of succeeded at(I think), but another requirement is to alter all adjustment quantity made in a certain type of location zone to be zero.

The attached script works fine, but when I try to insert an if statement to do so, it fails.

The if statement I've tried to write was:

if("Zone Code"]='ADJUST', '0',Quantity) as Quantity

If this is inserted anywhere within the load statement, the load fails.

What am I doing wrong in here? 🙂

Thanks for all help!

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:22:06 GMT

Hi Robert,

Just the syntax on your fieldname, try the below

if([Zone Code]='ADJUST', '0',Quantity) as Quantity

hope that helps

Joe

Re: if statement when loading data from db

maxgro — Wed, 18 Mar 2015 12:23:44 GMT

maybe

if([Zone Code]='ADJUST', 0, Quantity) as Quantity

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:33:14 GMT

I've inserted it as follows within the LOAD statement:

This is what I get when trying to reload or debug the script

I am pretty sure this is some dumb typo on my account, please could you help me?

Is there anything extra I could send your way to make it easier to pinpoint?

Thanks!

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:34:25 GMT

Same issue as in my reply to Joe above

Thanks!

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:37:47 GMT

Hi Robert

it does not look like your having problem with the if statement, but a problem with your OLEDB SQL.

Can u try post the full SQL string?

/Teis

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:41:39 GMT

This is embarrassing, but I need to ask, what exactly do you mean by full sql string [edit!]?

Please find the script in it's current state attached to this post!

Thanks,

Re: if statement when loading data from db

buzzy996 — Wed, 18 Mar 2015 12:43:24 GMT

in ur if statement their is a problem.

if("Zone Code"='ADJUST', '0',Quantity) as Quantity or

if([Zone Code]='ADJUST', '0',Quantity) as Quantity

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:49:32 GMT

This correction was suggested by Joe Simmons - the script still fails saying OLEDB read failed, there is a screenshot above

Thanks

Re: if statement when loading data from db

buzzy996 — Wed, 18 Mar 2015 12:52:56 GMT

might be case sensitive issue,

If([Zone Code]='Adjust','0','Siva') or

If([Zone Code]='adjust','0','Siva')

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

If("Zone Code"='Adjust','0','Siva') or

If("Zone Code"='adjust','0','Siva')

Re: if statement when loading data from db

Wed, 18 Mar 2015 12:59:21 GMT

Like Shiva suggest, and add UPPER() to be sure.

if( Upper( "Zone Code" ) = 'ADJUST', '0',Quantity) as Quantity

if( Upper( [Zone Code] ) =' ADJUST', '0',Quantity) as Quantity

Data in the fields are case sensitive!

/Teis

Re: if statement when loading data from db

Wed, 18 Mar 2015 13:06:06 GMT

Hi Robert

try this

Find the part in your load statement add replace the SQL select with only

SQL SELECT * FROM "TBW_LIVE".dbo."TBW$Warehouse Entry"

the * represent all fields in the database your are trying to load from

/Teis

Re: if statement when loading data from db

Wed, 18 Mar 2015 13:21:35 GMT

if( Upper( "Zone Code" ) = 'ADJUST', '0',Quantity) as Quantity

if( Upper( [Zone Code] ) =' ADJUST', '0',Quantity) as Quantity

Did not help as well.

I've loaded the data without the problematic if statement - and confirmed that the zone code is all uppercase ADJUST

So I believe this is not the issue.

If I would send you the whole script, would you be able to incorporate this if statement for me correctly? Maybe then I would figure out what am I doing wrong?

Thank you for all your help so far - I didn't expect that an if statement will cause me so much frustration!

The script is in qvs format is attached to this post!

Thanks again!

Re: if statement when loading data from db

Wed, 18 Mar 2015 13:37:14 GMT

Okey try this rename the result of your if statement to following:

if( Upper( "Zone Code" ) = 'ADJUST', '0',Quantity) as Quantity2

if( Upper( [Zone Code] ) = 'ADJUST', '0',Quantity) as Quantity2 //Remember no space in ''

It looks like u have 2 fields with the same name, that cant be done in same load.

ex. will fail.

Quantity,

if( Upper( "Zone Code" ) = 'ADJUST', '0',Quantity) as Quantity

/Teis

Re: if statement when loading data from db

Wed, 18 Mar 2015 13:54:10 GMT

Hi Teis,

I basically stripped the script to the most essential part which is needed, in this case:

WarehouseEntry:

LOAD

"Entry No_",

"Zone Code",

Quantity,

if([Zone Code]='ADJUST', '0',Quantity) as Quantity2;

SQL SELECT *

FROM "TBW_LIVE".dbo."TBW$Warehouse Entry";

It works!

So, looking at my data which I have loaded now

I can see that field Quantity2 does what I need to do, which is carry over the adjustment if the bin is not ADJUST, and insert 0 where the bin is ADJUST!

So, to avoid the problem, I've renamed the real Quantity field to be "Original Qty" and the changed field to be Quantity

LOAD

"Entry No_",

"Zone Code",

Quantity as "Original Qty",

if([Zone Code]='ADJUST', '0',Quantity) as Quantity;

SQL SELECT *

FROM "TBW_LIVE".dbo."TBW$Warehouse Entry";

Which lead to this

Which is what I wanted - Big thumbs up and thank you for your help!

Robert

Re: if statement when loading data from db

Wed, 18 Mar 2015 13:57:20 GMT

Hi Robert

Happy to help!

/Teis

Re: if statement when loading data from db

buzzy996 — Wed, 18 Mar 2015 14:09:26 GMT

haaaaaaaaaaaaaa,this is basic..but forgotten.