Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Are you not doing the Order By statement?
Hi Sunny,
I think I missed this earlier. Will try again. Thanks!
Can you also brief me about how your logic works.
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.
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.
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?
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