excluding and including values

[hamza99a]

Hello all,

I have a bunch of csv files that load in and store in a qvd. Recently, we added in a new field called OPEN_AMT. What I would like to do is to create an if statment that gathers data from the new field onward.

My if statement:

if (production date>='07/23/2018',OPEN_AMT,null())

However, when I type in OPEN_AMT it does not recognize it. The script runs but does not give me the open amt field.

Thank you and  any help would be appreciated!

[hamza99a]

stalwar1‌youssefbelloum‌sureshqv‌csg‌johnw‌olivierrobin‌

[sunny_talwar]

Did you mean to do this?

if (production date>='07/23/2018',OPEN_AMT,null()) as OPEN_AMT

[hamza99a]

yes im sorry I did that as well but it didnt work

[YoussefBelloum]

EDITED

Hi,

maybe it gives you null on all the rows..

did you gave the field an alias and put production date into [], like this ?

if ([production date]>='07/23/2018',OPEN_AMT,null()) as OPEN_AMT

also, what is your OPEN_AMT and [production date] format ? they should be the same..if it is DD/MM/YYYY, you can try to format it inside that script line, like this:

if (Date(Date#(production date,'DD/MM/YYYY'),'DD/MM/YYYY')>='07/23/2018',OPEN_AMT,null()) as OPEN_AMT

[sunny_talwar]

So, what exactly was the issue? You don't see a field name called OPEN_AMT or do you see all nulls in OPEN_AMT? If the values are all nulls... may be try this

If(production date >= MakeDate(2018, 7, 23), OPEN_AMT, Null()) as OPEN_AMT

[hamza99a]

with that code I am getting errors stating the field Production Date is not found.

[sunny_talwar]

Well what is your field name? production date or Production Date? Qlik is case sensitive... so make sure to use the correct casing. Also, youssefbelloum mentioned a good point... since you have space in the field name... wrap it around with  sqaure brackets []

[hamza99a]

hello youssef,

Unfortunately i get this error message:

The following error occurred:

Field 'OPEN_AMT' not found

The error occurred here:

?

The following error occurred:

Field 'Production Date' not found

The error occurred here:

?

The following error occurred:

Field 'Production Date' not found

[hamza99a]

So after ensuring the production date was used correctly I was able to load in data, however my OPEN_AMT is null across all dates. It should be a balance amount for 7/23 onwards

The following error occurred:

Field 'OPEN_AMT' not found

The error occurred here:

?