topic Re: Populate empty field with random ID? in QlikView

Populate empty field with random ID?

Fri, 08 Apr 2016 21:38:13 GMT

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 !!

Re: Populate empty field with random ID?

swuehl — Fri, 08 Apr 2016 23:25:05 GMT

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

Re: Populate empty field with random ID?

Fri, 08 Apr 2016 23:44:21 GMT

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

Thanks!!

Re: Populate empty field with random ID?

MarcoWedel — Fri, 08 Apr 2016 23:55:06 GMT

please post some sample Excel sheets and your required result.

A random character A-Z would be

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

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

hope this helps

regards

Marco

Re: Populate empty field with random ID?

swuehl — Sun, 10 Apr 2016 12:03:27 GMT

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