Creating a unique ID field

[s2016]

Hi,

I have a fact table data in daily QVD's (e.g. Traffic_20170827.qvd). Each file has multiple "entity_name_raw" and each day there are some new entity_name_raw. I need to assign a unique ID to each entity_name_raw.

I am using the below script to do that. Although this script runs fine on most of the days but sometimes a different ID is assigned to a entity_name_raw each day the script runs.

Entity_Bigpond_TID:

LOAD

          Distinct

          entity_name_raw

FROM

[$(vL.QVDDestPath)Traffic_*.qvd]

(qvd);

Entity_temp:

Load

          entity_name_raw,

          text('E_BT' & num(AutoNumber(entity_name_raw, 'auto1'), '000000000'))  as entity_id      

Resident Entity_Bigpond_TID;

Can anyone suggest a better way to achieve this?

Regards,

Shashank

[sunny_talwar]

Are you not doing the Order By statement?

[s2016]

Hi Sunny,

I think I missed this earlier. Will try again. Thanks!

Can you also brief me about how your logic works. 

[sunny_talwar]

Basically after you have loaded all your files into a QlikView table, you will need to take a resident load where you will sort the table by entity_name_raw and this will then be used here

If(entity_name_raw = Previous(entity_name_raw), Alt(Peek('RowNo'), 1), RangeSum(Peek('RowNo'), 1)) as RowNo

This logic is just checking if entity_name_row is equal to previous row or not. If it is, then use the same RowNo as before, else add 1 to the previous RowNo value in the sorted table.

[s2016]

Thanks for the explanation.

Basically what I want is consistent ID's for each Entity_Name_Raw.

eg) If  'xyz' has '0000100' assigned to it today, it should remain '0000100' all the times in future, just like you would expect in a database table.

I hope I am making my point clear. The end users want to use this info as a reference table with a consistent and unique id for each Entity_Name_Raw.

I am concerned that if the order changes in the sorted table (which definitely would once new Entity_Name_Raw are added), your code might not work as required. 

[sunny_talwar]

Are you saying that after each reload, the value should not be changing? May be store the value in a qvd and then only assign a value to a new Entity?

[sunny_talwar · Accepted solution]

I have created a sample for you...

Day 1

Table:

LOAD Entity

FROM

FirstFile.xlsx

(ooxml, embedded labels, table is Sheet1);

UniqueID:

LOAD Entity,

If(Entity = Previous(Entity), Peek('RowNo'), RangeSum(Peek('RowNo'), 1)) as RowNo

Resident Table

Order By Entity;

STORE UniqueID into UniqueID.qvd (qvd);

You run your main fact table and create a new table UniqueID where you create the ID (I have used the name RowNo)

Day 2 and Onwards (In the sample I have did Day2 and Day3... but the script is the same)

UniqueID:

LOAD Entity as EntityCheck,

    RowNo

FROM UniqueID.qvd (qvd);

MaxID:

LOAD Max(RowNo) as Max

Resident UniqueID;

LET vMaxID = Peek('Max');

TRACE $(vMaxID);

DROP Table MaxID;

Table:

LOAD Entity

FROM

SecondFile.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

NoConcatenate

LOAD Entity as EntityCheck,

If(Entity = Previous(Entity), Peek('RowNo'), RangeSum(Alt(Peek('RowNo'), $(vMaxID)), 1)) as RowNo

Resident Table

Where not Exists(EntityCheck, Entity)

Order By Entity;

FinalUniqueID:

LOAD EntityCheck as Entity,

RowNo

Resident UniqueID;

DROP Table UniqueID;

Concatenate(FinalUniqueID)

LOAD EntityCheck as Entity,

RowNo

Resident Temp;

STORE FinalUniqueID into UniqueID.qvd (qvd);

RENAME Table FinalUniqueID to UniqueID;

DROP Table Temp;

So, basically Day 1 is needed one time to initiate the qvd building process. Once created, you will always need to run Day 2 and onward script...

In the sample just run the script for one day at a time and comment the other days and start with Day 1.

Best,

Sunny