topic Autonumber period in QlikView

Autonumber period

EsmeeM — Sat, 16 Nov 2024 01:57:00 GMT

Hi,

I want to create periodIDs with the autonumber function. However, using the syntax below the master calendar returns 20+ duplicate rows (see screenshot as attached). I'm new to Qlikview and would really appreciate it if someone could help me out and tell me what is going wrong here 🙂

I have used the following syntax:

//--- Select the lowest and highest periods

Temp_Calendar_Range:
LOAD
Num(Date#(Min(Period), 'YYYYMM')) as MinDate,
Num(Date#(Max(Period), 'YYYYMM')) as MaxDate

RESIDENT [Availability];

//--- Assign the start and end dates to variables
LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

DROP TABLE Temp_Calendar_Range; // Cleanup

[Master Calendar]:

Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID]
;

LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;

LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//--- Remove the temporary variables
LET vMinDate = Null();
LET vMaxDate = Null();

Re: Autonumber period

sunny_talwar — Thu, 14 Nov 2019 12:20:11 GMT

What AutoNumber does is that if a value is repeated, it assigns it with the same value... for example everytime autonumber sees 2018Q1... it will return the same number... which is why QuarterID is always equal to 1 when Year&Quarter = 2018Q1 from your example.... is this not what you want?

Re: Autonumber period

Arthur_Fong — Thu, 14 Nov 2019 12:22:05 GMT

Is your Period a date field in numeric? Or in text?

If it is in text,

Num(Date#(Min(Period), 'YYYYMM')) as MinDate,
Num(Date#(Max(Period), 'YYYYMM')) as MaxDate

The min and max here couldn't work as text field cannot be min or max.

Try:

Num(Min(Date#(Period, 'YYYYMM'))) as MinDate,
Num(Max(Date#(Period, 'YYYYMM'))) as MaxDate

If your date field is in numeric,

Try:

Min(Period) as MinDate,
Max(Period) as MaxDate

Re: Autonumber period

EsmeeM — Tue, 19 Nov 2019 12:11:21 GMT

Thanks for your replies!

However, my problem is that the autonumber function returns many duplicates. I have not found a solution to fix it...

Is there a function for script that removes the duplicates from the table?

Re: Autonumber period

Arthur_Fong — Tue, 19 Nov 2019 12:29:27 GMT

Try:

Load *,
AutoNumber(Quarter) as [QuarterID],
AutoNumber(Period) as [PeriodID]
;

Re: Autonumber period

EsmeeM — Tue, 19 Nov 2019 12:40:24 GMT

Thank you for the quick reply.

I tried it, but still same results...

Re: Autonumber period

Arthur_Fong — Tue, 19 Nov 2019 12:49:31 GMT

LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
//Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;

Comment Year-Month and try again.

Re: Autonumber period

Arthur_Fong — Tue, 19 Nov 2019 12:56:44 GMT

To create Year-Month field, try this:

Load *,Year &'-'& Month as [Year - Month];

LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;

Re: Autonumber period

Brett_Bleess — Tue, 19 Nov 2019 14:28:24 GMT

Esméé, did Arthur's latest posts get you what you needed? If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that helped you get things working properly. If you are still working on things, leave an update for us.

Regards,
Brett

Re: Autonumber period

EsmeeM — Wed, 20 Nov 2019 07:55:31 GMT

Thanks Arthur,

I've tried it, but unfortunately the table still returns duplicates of rows.