Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
......
I can't think of any reason why you would need the where clause. Do you get an error message or something?
DATE1 > DATEX will not work as DATEX does not exist in the table TABLE1
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)
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
......
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