Qliksense two table date between compare

[krmvacar]

Hi All,

I have two tables and here i want to compare date between tables.

If the date field in the first table is between the dates in the second table according to the ID field, I want NAME and FLAG to be next to them.

Example: ID=17027 test1.date between test2.date1 and test2.date2 FLAG and NAME come left side.

Thanks for helping.

Best Regards  

test1:

Date	ID	TOTAL
03.01.2022	17027	14.51
12.01.2022	17033	13.96
21.03.2022	17041	9.51
01.03.2022	17041	9.51

test2:

Date1	Date2	ID	NAME	FLAG
01.01.2022	05.01.2022	17027	JOHN	1
01.01.2022	02.01.2022	17027	JOHN	0
11.01.2022	15.01.2022	17033	OLIVER	1
03.01.2022	11.01.2022	17033	OLIVER	0
16.03.2022	19.03.2022	17041	SAM	0
20.03.2022	25.03.2022	17041	SAM	1

result:

Date	ID	TOTAL	NAME	FLAG
03.01.2022	17027	14.51	JOHN	1
12.01.2022	17033	13.96	OLIVER	1
21.03.2022	17041	9.51	SAM	1
01.03.2022	17041	9.51	SAM	0

[QFabian]

Hi @krmvacar , check this script, in your Result table appears  a 4th row, but according to formula it shouldnt be there.

test1:
Load * INLINE [
Date, ID, TOTAL
03.01.2022, 17027, 14.51
12.01.2022, 17033, 13.96
21.03.2022, 17041, 9.51
01.03.2022, 17041, 9.51
];

test2:
Load * INLINE [
Date1, Date2, ID, NAME, FLAG
01.01.2022, 05.01.2022, 17027, JOHN, 1
01.01.2022, 02.01.2022, 17027, JOHN, 0
11.01.2022, 15.01.2022, 17033, OLIVER, 1
03.01.2022, 11.01.2022 17033, OLIVER, 0
16.03.2022, 19.03.2022, 17041, SAM, 0
20.03.2022, 25.03.2022, 17041, SAM, 1
];

inner Join IntervalMatch ( Date, ID )
LOAD
Date1, Date2, ID
Resident test2;

drop table test1;

exit script;

Result Table :

[krmvacar]

Hİ @QFabian ,

Thank you so much for help me.

That it working but  How can I add the total value from 1 table? 

we just forgot only Total value.

Date	ID	TOTAL	NAME	FLAG
03.01.2022	17027	14.51	JOHN	1
12.01.2022	17033	13.96	OLIVER	1
21.03.2022	17041	9.51	SAM	1
01.03.2022	17041	9.51	SAM	0

Best Regards.

[Mario_De_Felipe · Accepted solution]

Hi @krmvacar,

you can get it, joining again:

test1:
Load * INLINE [
Date, ID, TOTAL
03.01.2022, 17027, 14.51
12.01.2022, 17033, 13.96
21.03.2022, 17041, 9.51
01.03.2022, 17041, 9.51
];

test2:
Load * INLINE [
Date1, Date2, ID, NAME, FLAG
01.01.2022, 05.01.2022, 17027, JOHN, 1
01.01.2022, 02.01.2022, 17027, JOHN, 0
11.01.2022, 15.01.2022, 17033, OLIVER, 1
03.01.2022, 11.01.2022 17033, OLIVER, 0
16.03.2022, 19.03.2022, 17041, SAM, 0
20.03.2022, 25.03.2022, 17041, SAM, 1
];

inner Join IntervalMatch ( Date, ID )
LOAD
Date1, Date2, ID
Resident test2;

left join (test2)

LOAD *, '' as dummy RESIDENT test1;

DROP TABLE test1;

[PrashantSangle]

Hello @krmvacar ,

Today I saw your message, also I saw you already got the correct reply.

As I am not huge fan of IntervalMatch(), Internally it will create cross join with itself, So when your data set is huge date range then you will face performance issue. So sharing another solution. where I used while loop

Find below solution

test2:
Load Date(Date#(Date1,'DD.MM.YYYY'),'DD.MM.YYYY') as Date1,
Date(Date#(Date2,'DD.MM.YYYY'),'DD.MM.YYYY') as Date2,
ID,
NAME,
FLAG
INLINE [
Date1, Date2, ID, NAME, FLAG
01.01.2022, 05.01.2022, 17027, JOHN, 1
01.01.2022, 02.01.2022, 17027, JOHN, 0
11.01.2022, 15.01.2022, 17033, OLIVER, 1
03.01.2022, 11.01.2022 17033, OLIVER, 0
16.03.2022, 19.03.2022, 17041, SAM, 0
20.03.2022, 25.03.2022, 17041, SAM, 1
];

NoConcatenate

test1:
Load Date(Date#(Date,'DD.MM.YYYY'),'DD.MM.YYYY') as Date,
ID,
TOTAL
INLINE [
Date, ID, TOTAL
03.01.2022, 17027, 14.51
12.01.2022, 17033, 13.96
21.03.2022, 17041, 9.51
01.03.2022, 17041, 9.51
];

Left Join
Load ID,
Date(Date1 + IterNo() - 1,'DD.MM.YYYY') as Date,
NAME,
FLAG
Resident test2
While Date1 + IterNo() - 1 <= Date2;

drop Table test2;

Regards,

Prashant Sangle

[krmvacar]

Thank you so much @PrashantSangle