topic dropping the records in App Development

dropping the records

KNRaju105630 — Wed, 10 May 2023 17:42:10 GMT

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!

Re: dropping the records

Chanty4u — Wed, 10 May 2023 18:01:05 GMT

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

Re: dropping the records

KNRaju105630 — Wed, 10 May 2023 18:07:05 GMT

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

Re: dropping the records

KNRaju105630 — Wed, 10 May 2023 18:34:49 GMT

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

Re: dropping the records

KNRaju105630 — Thu, 11 May 2023 01:36:51 GMT

@tresB help please

Re: dropping the records

tresB — Thu, 11 May 2023 05:43:44 GMT

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