Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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 :
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.
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;
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
Thank you so much @PrashantSangle