Assigning rows a unique ID

waleeed_mahmood — Sat, 16 Nov 2024 18:48:38 GMT

Hi everyone,

I have a Qlik related problem i cant seem find any help on.

I have a Table called Table A. It has around 20 columns and 26000 rows. From that table i extract a new table with the fields i need, as shown below:

To extract the above table from Table A, i used the following Script:

load Distinct("F MPG") as MPG, EFlag as ef, RFlag as rf, [SAP Material] as SAPM, "C L2 NP" as L2, "F MC" as MC, Owner as owner, '0%' as "Planned Change", FSIML2NP as FSIM Resident Material_Master_Table;

What i want to do, is assign a row number (1-to-n) for all the rows in my extracted table. From my experiments, i cant seem to use Rowno() or RecNo() becuase i am using DISTINCT in my extract script as shown above. So, if i were to use RowNo()/RecNo() it would make no sense. Even if i wasnt using distinct in my script, i still would not want to have the RowNo/RecNo from the original table. I have tried using AutoNumber(SAPM,FMPG) and few other variations of autonumber, but so far no luck.

About my fields:

For each MPG, there could be multiple MCs as shown above.

SAPM field has unique entry for each row (its a product number).

Multiple MPGs/MC can belong to one Owner.

L2 and FSIM are just numbers that i use in other calculations.

OUTPUT:

it would be similar to picture shown, except there would be an ID column which has unique number for each row of that table.

ID	Owner	MPG	MC	PlannedChange	Actual Change
1	001	8A	AS4....	0	..%
2	001	8A	AS4....	0	..%
3	001	8A	AS4....	0	..%
4	001	8A	AS4....	0	..%
5	001	8A	AS4....	0	..%

Note: I didnt type all the values, but this should give the output idea.

I'd really appreciate the help!!!!

Please let me know if you need more information.

Thank you.

Re: Assigning rows a unique ID

Lisa_P — Thu, 09 Apr 2020 03:44:59 GMT

Try using Autonumber feature:

load Distinct("F MPG") as MPG, Autonumber("F MPG") as ID, EFlag as ef, RFlag as rf, [SAP Material] as SAPM, "C L2 NP" as L2, "F MC" as MC, Owner as owner, '0%' as "Planned Change", FSIML2NP as FSIM Resident Material_Master_Table;

Re: Assigning rows a unique ID

waleeed_mahmood — Thu, 09 Apr 2020 16:42:01 GMT

Hi Lisa,

Thank you for your reply. I had to change "F MPG" in autonumber to "F MC" since F MPG isnt a unique value i believe. But, in the end it worked fine for me.

Thank you for your help.

topic Re: Assigning rows a unique ID in App Development

Assigning rows a unique ID

Re: Assigning rows a unique ID

Re: Assigning rows a unique ID