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

[jduluc12]

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

[mageste]

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:

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