Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
my records are dropping when using Interval Match (my requirement should not be drop any record)
Let vMinDate = Num(Date#('01/01/2021','MM/DD/YYYY'));
Let vMaxDate = Num(Date(Today(),'MM/DD/YYYY'));
Master_Calendar:
LOAD Date(DateMap, 'MM/DD/YYYY') as DateMap;
Load *,
makeDate(Year(DateField), Month(DateField) ,01) as DateMap;
Load
Date($(vMinDate) + IterNo() - 1) as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
Sample:
LOAD rowno() as Seq,
Category,
Product,
Date#([Hire Date], 'MM/DD/YYYY') as [Hire Date],
Date#([Leave Date], 'MM/DD/YYYY') as [Leave Date];
Load * inline [
Category, Product, Hire Date, Leave Date
Qlik, QV, 12/31/2021, 06/30/2022
Qlik, QS, 06/16/2022, 06/28/2022
Qlik, N-Printing, 07/10/2022, 08/31/2023
Qlik, ODAG, 08/10/2022, 08/25/2022
];
inner join
IntervalMatch(DateMap)
Load [Hire Date],
[Leave Date]
Resident Sample;
Thanks in Advance!
You can use left join instead of intervalmatch
Let vMinDate = Num(Date#('01/01/2021','MM/DD/YYYY'));
Let vMaxDate = Num(Date(Today(),'MM/DD/YYYY'));
Master_Calendar:
LOAD Date(DateMap, 'MM/DD/YYYY') as DateMap;
Load *,
makeDate(Year(DateField), Month(DateField), 01) as DateMap;
Load
Date($(vMinDate) + IterNo() - 1) as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
Sample:
LOAD rowno() as Seq,
Category,
Product,
Date#([Hire Date], 'MM/DD/YYYY') as [Hire Date],
Date#([Leave Date], 'MM/DD/YYYY') as [Leave Date];
Load * inline [
Category, Product, Hire Date, Leave Date
Qlik, QV, 12/31/2021, 06/30/2022
Qlik, QS, 06/16/2022, 06/28/2022
Qlik, N-Printing, 07/10/2022, 08/31/2023
Qlik, ODAG, 08/10/2022, 08/25/2022
];
Left Join (Sample)
Load Category,
Product,
DateMap
Resident Master_Calendar;
Drop Table Master_Calendar; // Optional: Drop the
temporary Master_Calendar table
If use left join - Interval not create for those records (my scenario that should create for interval for all the records)
Experts, if any suggestions or work around - that would be great.
@tresesco help please
I didn't check completely, but it seems that your calendar script is not fine. Try commenting out the two lines (and changing a date field as highlighted) like:
Master_Calendar:
LOAD Date(DateField, 'MM/DD/YYYY') as DateMap;
//Load *,
//makeDate(Year(DateField), Month(DateField) ,01) as DateMap;
Load
Date($(vMinDate) + IterNo() - 1) as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);