Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abarbier
New Contributor

Resident where on an other table

Hello,

i have one table

For some reason i have to  load this table with a where clause like this:

TABLE:

LOAD

    ID,

    DATEX,   

    CREF&BP as %key

FROM lib

WHERE not(DSTA=1 and OSTA=2);

Now i want to add at this table the items where DSTA=1 and OSTA=2 and the date must be > at the date where not(DSTA=1 and OSTA=2) and the compare must be done on CREF&BP.

So i tried something like that


NoConcatenate

TABLE1:

LOAD

    ID,

    DATEX as DATE1,   

    CREF&BP as %key1

FROM lib

WHERE DSTA=1 and OSTA=2;

Concatenate(TABLE)

     ID,

    DATE1 as DATEX,   

    %key1 as %key

resident TABLE1

WHERE not Exists(%KEY,%KEY1) and DATE1 > DATEX;

But Qlik sense say DATEX not exist

Help please

1 Solution

Accepted Solutions
MVP
MVP

Re: Resident where on an other table

I think you can add the TABLE date to TABLE1 (I assume CREF&BP is the key), bold in the script below


NoConcatenate

TABLE1:

LOAD

    ID,

    DATEX as DATE1, 

    CREF&BP as %key1

FROM lib

WHERE DSTA=1 and OSTA=2;

left join (TABLE1) LOAD

    CREF&BP as %key1,

    DATEX as DATE2

  resident TABLE;

and then concatenate with the DATE1 > DATE2 condition

......

5 Replies
rittermd
Honored Contributor

Re: Resident where on an other table

I can't think of any reason why you would need the where clause.  Do you get an error message or something?

shivanandk
Contributor II

Re: Resident where on an other table

DATE1 > DATEX will not work as DATEX does not exist in the table TABLE1

cheenu_janakira
Contributor

Re: Resident where on an other table

Shivanand is right. Only Date1 exists in your 2nd table whilst loading (Datex exists once the table is loaded and the field aliasing is complete). Please explain what you are trying to do, as your script seems a bit convoluted.

Tcho,

C  ;o)

MVP
MVP

Re: Resident where on an other table

I think you can add the TABLE date to TABLE1 (I assume CREF&BP is the key), bold in the script below


NoConcatenate

TABLE1:

LOAD

    ID,

    DATEX as DATE1, 

    CREF&BP as %key1

FROM lib

WHERE DSTA=1 and OSTA=2;

left join (TABLE1) LOAD

    CREF&BP as %key1,

    DATEX as DATE2

  resident TABLE;

and then concatenate with the DATE1 > DATE2 condition

......

abarbier
New Contributor

Re: Resident where on an other table

Oh sorry i don t explain my intention.

I want to add a flag to my table so when i create TABLE i add 0 as flag.

Now i want to add 1 as flag to all data with DSTA=1 and OSTA=2 where for the key he haven t clone with date > or =. It have to delete all over data

Example i have this datas

    ID    DATEX    CREF    BP      DSTA      OSTA

    1      10-30-17  SMITH  330        1               2

     2     11-15-17  SMITH  200         3              2

     3     11-15-17   JOBS   150         1              2

     4     11-15-17   JOBS   150         2              2

     5     11-15-17    BYE     660        1              2 

     6     10-01-17    BYE     660        2              2

     7     10-30-17   MONK  550         1              2

     8     11-15-17   MONK  550         3              2


It should give me :

    

    ID    DATEX    CREF    BP      DSTA      OSTA     FLAG

    1      10-30-17  SMITH  330        1               2          1

     2     11-15-17  SMITH  200         3              2          0

     3     11-15-17   JOBS   150         1              2          1

     4     11-15-17   JOBS   150         2              2          0

     5     11-15-17    BYE     660        1              2          1

     6     10-01-17    BYE     660        2              2          0

     8     11-15-17   MONK  550         3              2          0


Thanks

I will try something with your solution Massimo

Community Browser