Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Populate empty field with random ID?

Hi! I'm gathering different Excel Sheets from a third party which have no Unique field except for the title and i'm fearing that these might repeat themselves eventually. Is there any way that Qlikview can autocomplete blank fields with unique ID's every time it loads?

I plan to include new excel spreadsheets every week, so I won't have time to do this myself. Any clues? I was thinking something like...

"If Title already exists, Add random character from A-Z at the end and use it as ID".

Or maybe it's even simpler... and I can just auto complete a blank column with random values for each load.

Thank you very much !!

4 Replies
swuehl
MVP
MVP

Not sure if I understand your request and setting properly.

Maybe just create a new field with unique values for each row using Recno() / Rowno()?

LOAD Title,

          Recno() as RecID,

...

FROM ...;

Not applicable
Author

Awesome!!! Would that work for different files?? Like... if it loads 10 spreadsheets with 1000 records, it generates the missing ID??

Thanks!!

MarcoWedel

please post some sample Excel sheets and your required result.

A random character A-Z would be

Chr(64+Ceil(Rand()*26)) as Character

QlikCommunity_Thread_212691_Pic1.JPG

not sure how you want to use it though ...

hope this helps

regards

Marco

swuehl
MVP
MVP

If you concatenate the tables and if you want to have a unique ID per line, I think you should go with RowNo()

Counters in the Load