Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jduluc12
Creator
Creator

Showing a record even if there is no corresponding record in the other table

Hi

I got two tables.

Table-1:

Item Date-1
ITEM-1 01/02/2023
ITEM-1 01/03/2023
ITEM-2 01/03/2023
ITEM-2 01/10/2023
ITEM-3 01/04/2023
ITEM-4 01/02/2023

 

Table-2:

Item Date-2
ITEM-1 02/04/2023
ITEM-1 01/01/2023
ITEM-2 02/04/2023
ITEM-2 02/05/2023
ITEM-3 01/02/2023

 

The expected output is 

Item Count
ITEM-1 1
ITEM-2 2
Item-4 0

 

the logic of the output is

if (Date-1 < Date-2) or  (There is no record in Table-2) then show the item

Item-1 has one record where  (Date-1 < Date-2)  is true, so it appears in the Output, and the count is 1.

Item-2 has two records where  (Date-1 < Date-2)  is true, so it appears in the Output, and the count is 2.

Item-3 has no record where (Date-1 < Date-2)  is true, so it does not appear in the Output.

Item -4 has no record in Table-2, so it appears in the output.

 

If putting Item-4 in the same output proves to be very challenging then we can have two output tables.

one with this condition (Date-1 < Date-2) 

other one with (There is no record in Table-2) 

 

thanks

Labels (1)
1 Reply
mageste
Partner - Contributor III
Partner - Contributor III

Hello there!

I could only think of a way by joining the tables in the script editor.

This is how I structured it:

 

Table_1:
LOAD * Inline [
Item ,Date-1
ITEM-1 ,01/02/2023
ITEM-1 ,01/03/2023
ITEM-2 ,01/03/2023
ITEM-2 ,01/10/2023
ITEM-3 ,01/04/2023
ITEM-4 ,01/02/2023
];

Left Join(Table_1)
Table_2:
LOAD * Inline [
Item ,Date-2
ITEM-1 ,02/04/2023
ITEM-1 ,01/01/2023
ITEM-2 ,02/04/2023
ITEM-2 ,02/05/2023
ITEM-3 ,01/02/2023
];


Final_Table:
LOAD
Item,
"Date-1",
"Date-2",
If("Date-1" < alt("Date-2",0), 1, if(IsNull("Date-2"),1,0)) as Flag
Resident Table_1;

Drop Table Table_1;

 

Then I was able to build a table with the desired output using a COUNT expression:

count_table.png

 

I wasn't so sure if I have fully understood your business rules, but please let me know if this helps.