Conditional load. If Condition Concatinate Load

[pgriffiths]

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

[pgriffiths]

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)

[maxgro]

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()));

[jerem1234 · Accepted solution]

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!

[pgriffiths]

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.