Autonumber period

[EsmeeM]

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

[sunny_talwar]

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?

[Arthur_Fong]

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

[EsmeeM]

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?

[Arthur_Fong]

Try:

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

[EsmeeM]

Thank you for the quick reply.

I tried it, but still same results...

[Arthur_Fong]

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.

[Arthur_Fong]

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

[Brett_Bleess]

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

[EsmeeM]

Thanks Arthur, 

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