Assigning rows a unique ID

[waleeed_mahmood]

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.

[Lisa_P · Accepted solution]

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;

[waleeed_mahmood]

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.