topic Re: Conditional load. If Condition Concatinate Load in QlikView

Conditional load. If Condition Concatinate Load

pgriffiths — Tue, 30 Sep 2014 20:23:59 GMT

Hi all,

I have been trying to get this to work for ages and can't seem to work it out.

I have a CSV file with dates in it in the format DD/MM/YYYY.

I load the file and I then want to concatenate a new row with todays date, but only if it does not already exist.

This is what I am working with to figure this out.

UNQUALIFY *;

History:

LOAD * INLINE [

History.Date

27/09/2014

];

DateRange:

Load max(History.Date)as DateRange.UpperDate

Resident History;

//Only load todays data if it doesn't already exist.

if (num(today()-DateRange.UpperDate) > 0) then

Concatenate(History)

LOAD * INLINE [

History.Date

'30/09.2014'

];

end if

Re: Conditional load. If Condition Concatinate Load

pgriffiths — Tue, 30 Sep 2014 20:28:22 GMT

The IF statement condition

num(today()-DateRange.UpperDate)

Returns 3 when output in a text box so I do not see why the following doesn't trigger the IF statement

(num(today()-DateRange.UpperDate) > 0)

Re: Conditional load. If Condition Concatinate Load

maxgro — Tue, 30 Sep 2014 20:30:15 GMT

History:

LOAD * INLINE [

History.Date

27/09/2014

];

// add today if missing

Concatenate (History)

load

date(floor(Today())) as History.Date

AutoGenerate 1

where

not exists (History.Date, floor(Today()));

Re: Conditional load. If Condition Concatinate Load

jerem1234 — Tue, 30 Sep 2014 20:36:39 GMT

You can't reference the field DateRange.UpperDate outside of the table like you have it. You'll need to store it in a variable right after like:

History:

LOAD * INLINE [

History.Date

27/09/2014

];

DateRange:

Load max(History.Date)as DateRange.UpperDate

Resident History;

Let vMax = peek('DateRange.UpperDate');

Drop Table DateRange;

//Only load todays data if it doesn't already exist.

if (num(today()-vMax) > 0) then

Concatenate(History)

LOAD * INLINE [

History.Date

30/09/2014

];

ENDIF

Let vMax = Null();

Hope this helps!

Re: Conditional load. If Condition Concatinate Load

pgriffiths — Wed, 01 Oct 2014 10:07:59 GMT

Thank you,

I don't know how many different ways I tried to get this to work.

The variable way works great.

Thank you so much.