if statement when loading data from db

[#1]

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!

[#2]

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

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

or

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

Data in the fields are case sensitive!

/Teis

[#3]

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

[#4]

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!

[#5 · Accepted solution]

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

[#6]

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

[#7]

Hi Robert

Happy to help!

/Teis

[buzzy996]

haaaaaaaaaaaaaa,this is basic..but forgotten.