dropping the records

[KNRaju105630]

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!

[Chanty4u]

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

[KNRaju105630]

If use left join - Interval not create for those records  (my scenario that should create for interval for all the records)

[KNRaju105630]

Experts, if any suggestions or work around - that would be great.

[KNRaju105630]

@tresesco  help please 

[tresesco]

@KNRaju105630 ,

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);