Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcobe13
Contributor III
Contributor III

Create unique id with the master calendar

Hello everyone! I'm here with another question ...

I've looked everywhere on the forum but have not found a solution.

my goal is to create a unique id connected to the master calendar.

I need:

- A self-generated based on calendar month and year (no day)

- Or: a complete calendar that you are creating, creates an id for each month + years.

what I mean?

auto generated id: (date starting on 01/01/2015 and ends 31/07/2016)

-201501

-201502

-201503

-201504

-201505

-201506

-201507

-201508

-201509

-201510

-201511

-201512

-201601

-201602

-201603

-201604

-201605

-201606

-201607

The id should not be repeated during creation, so if January 2015 was 31 days, I would not repeat the id 201 501 31 times, but only once.

If you can not tie this id to the calendar, you just can generate this id automatically from a date.

can someone help me?

The expression I use to create the id is this:

Num(year(TempDate),'0000') & Num(month(TempDate),'00') as UNIQUEID;

thanks to those who can help me!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

TableWIthUniqueID:

LOAD DISTINCT

     Autonumber(MonthStart(TempDate)) as ID

RESIDENT

     TableThatContainsTempDate

     ;


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

Autonumber(MonthStart(TempDate)) as ID


talk is cheap, supply exceeds demand
marcobe13
Contributor III
Contributor III
Author

thanks for the reply!

I had already tried this expression, but unfortunately I duplicate keys.

The expression in fact creates an id for each day of the month:

January 1, 2016 -> ID = 201601

January 2, 2016 -> ID = 201601

January 3, 2016 -> ID = 201601

repeated 31 times.

if I make a distinct count on the id I find 19 keys. (From January 2015 to July 2016).

if I make a normal count, I find 578 id, one for each day of the year.

I need an expression that I produce only 19 distinct keys.

Gysbert_Wassenaar

The keys aren't duplicated. Every record of the month gets the same key. I guess I don't understand what you're trying to do. Are you trying to create a table that has no relationship at all to any other data in your document? If so, what on earth for?


talk is cheap, supply exceeds demand
marcobe13
Contributor III
Contributor III
Author

I want to create a unique id, based on the union of the year and month, using the master calendar.

This is because I would like to create it based on the reference date used in the master calendar.

If you can not, simply create this field in a new table, based on the date that I use for the calendar.

The id that I create is the unique union between the year and month, in a progressive manner.

sorry if I have not explained well I hope I have explained better what I would get ...

I have used and tested your expression (when creating the master table calendar), but if I do a count on the id does not return 19 unique fields.

Gysbert_Wassenaar

So, you want a master calendar that only contains months and not dates?


talk is cheap, supply exceeds demand
marcobe13
Contributor III
Contributor III
Author

maybe, I do not know if it's the solution cher try.

The priority is to create this id automatically and dynamically, based on the reference date.

Do not use the days for analysis, then, perhaps create a master calendar days may not be the solution, but only if I can compose this unique ID.

Gysbert_Wassenaar

TableWIthUniqueID:

LOAD DISTINCT

     Autonumber(MonthStart(TempDate)) as ID

RESIDENT

     TableThatContainsTempDate

     ;


talk is cheap, supply exceeds demand
marcobe13
Contributor III
Contributor III
Author

I changed a little formula to get the id that I wanted, I added extracting a distinct month and year, the process is just that. So I have the calendar I wanted ... Thank you very much!