topic Re: Filtering out null data after join in QlikView

Filtering out null data after join

Wed, 31 Dec 2014 08:52:30 GMT

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;

Re: Filtering out null data after join

alkesh_sharma — Wed, 31 Dec 2014 09:00:27 GMT

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;

Re: Filtering out null data after join

maxgro — Wed, 31 Dec 2014 09:10:45 GMT

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');

Re: Filtering out null data after join

Wed, 31 Dec 2014 09:41:58 GMT

Doesn't change a thing

Re: Filtering out null data after join

avinashelite — Wed, 31 Dec 2014 09:44:03 GMT

can you please share the app.

Re: Filtering out null data after join

Wed, 31 Dec 2014 12:03:52 GMT

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