Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Filtering out null data after join

Hej guys,

I'm joining three tables in SQL but after doing so I cannot seem to filter out null values in the new table. Even when using the where clause I get one record for each date before 30-12-2014 with null values for every field. I have the following script:


Temp:
Load
Id,
Run,
Date(Floor(Timestamp(Run, 'D-M-YYYY h:mm:ss')), 'D-M-YYYY') as Date;
SQL SELECT Id, Date
FROM;

Left Join
Load
Id,
Id2;
SQL SELECT Distinct Id, Id2
FROM
Where Status= 'Done' or Status = 'Finished';

Left Join
Load
Id2,
Field1,
Field2,
Number;
SQL SELECT Id2, Field1, Field2, Number
FROM;

Final:
Load
Run&Field1&Field2 as UniqueId,
Date,
Field1,
Field2,
Number
Resident Temp
Where Date >= Date('30-12-2014 0:00:00','D-M-YYYY h:mm:ss');

Drop Table Temp;

5 Replies
alkesh_sharma
Not applicable

Re: Filtering out null data after join

Try This,

Temp:
Load
Id,
Run,
Floor(DATE(Run, 'DD-MM-YYYY')) as Date;
SQL SELECT Id, Run
FROM;

Left Join
Load
Id,
Id2;
SQL SELECT Distinct Id, Id2
FROM
Where Status= 'Done' or Status = 'Finished';

Left Join
Load
Id2,
Field1,
Field2,
Number;
SQL SELECT Id2, Field1, Field2, Number
FROM;

Final:
Load
Run&Field1&Field2 as UniqueId,
Date,
Field1,
Field2,
Number
Resident Temp
Where Date >= Floor(Date(30-12-2014,'DD-MM-YYYY');

Drop Table Temp;

maxgro
Not applicable

Re: Filtering out null data after join

replace

Where Date >= Date('30-12-2014 0:00:00','D-M-YYYY h:mm:ss');

with

where Date >= Date#('30-12-2014','D-M-YYYY');

Not applicable

Re: Filtering out null data after join

Doesn't change a thing

avinashelite
Not applicable

Re: Filtering out null data after join

can you please share the app.

Not applicable

Re: Filtering out null data after join

Nvm, solved it.

Apparently this was happening because I was using Date in another loaded table as well. Changed that one to Date2. Thanks for the time and responses at least