Resident where on an other table

[Anonymous]

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

[rittermd]

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

[shivanandk]

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

[cheenu_janakira]

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)

[maxgro · Accepted solution]

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

......

[Anonymous]

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