Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

if in data load editor

Hi, I'm having some issues with my if statements again, this time I'm in the data load editor attempting to flag if DATE3 is the same as either DATE1 or DATE2. What I think my problem is that DATE3 is from a different table than DATE1 and DATE2 so I get an error when I attempt to make the if-statement in either of their load locations.

LOAD DATE1,

  DATE2,

    DATE3,

    date (DATE1),

    date (DATE2),

    date (DATE3),

    if (DATE1 = DATE3, 1, 0) as NEW,

    if (DATE2 = DATE3, 1, 0) as CLOSE

I convert them to dates just because they specify an en exact time of day, which makes the comparison unnecessarily precise.

Thanks in advance

6 Replies

Re: if in data load editor

You can use only one source table in a load statement (disregarding wildcard loads from files). You will first have to create a new table containing all the fields you need by joining the source tables.

T1:

LOAD A, B FROM source_a;

JOIN (T1)

LOAD A, C FROM source_b;

T2:

LOAD A, if(B=C,1,0) as D RESIDENT T1;


talk is cheap, supply exceeds demand
Not applicable

Re: if in data load editor

When attempting to do this I get the following error:
LOAD statement only works with lib:// paths in this script mode

I'm loading everything through an ODBC connection if that helps.

Re: if in data load editor

What I posted is just an example to explain the join concept. I hope you didn't copy/paste that literally.

If you have problem loading data from ODBC source, have a look at this video: Data Load Editor - Loading ODBC Data (video)


talk is cheap, supply exceeds demand
Not applicable

Re: if in data load editor

The example you posted I used as a suggestion how to do it, I assumed T1 ment Table1, source a as the place i took my first set of data from etc etc.

Sadly I can't try anymore since I had to leave the office, I will look at the video and try and solve the issue over the weekend, thank you very much for your help

mov
Esteemed Contributor III

Re: if in data load editor

I think the "unnecessarily precise" is the key here.  Try

if (floor(DATE1) = floor(DATE3), 1, 0) ...

Edit: You were comparing timestamps before converting to the whole dates, and the timestamps are almost never equal.  Next, the correct way of removing time part is to use floor() function.  Date() function affetcs presentation not the actual values.  The right way is

date(floor(timestampfield)) as datefield

Not applicable

Re: if in data load editor

this is what my source a looks like.
LOAD DATE1,DATE2 FROM "SOURCE SERVER".dbo."SOURCE TABLE";

Community Browser