Add a unique identifier with nulls in the table

[smarties]

Below, a test data set, unfortunately doesn't give me the behaviour I need. The answer escapes me. Any insights would be appreciated.

The calculation, not unexpectedly, will not execute when Lot is a null.

I need to run the calculation for each line, but I don't have the ability to create a guaranteed unique identify. I don't see a way to add a Row number, again, because I don't have a way to create a guaranteed unique identify for each row.

Thanks.

Parts:
Load * Inline
[
Part, Factor
ABC, 400
DEF, 400
GHI, 400
];

Left Join (Parts)
Load * Inline
[
Part, Lot, Price, Adder
ABC, 123, 1.45, 2022
ABC, 987, 0.56, 2020
DEF, 456, 0.02, 2022
];

Left Join (Parts)
Load
Part, Lot,
If(IsNull(Price), Factor, Price + Adder) as Result
Resident Parts;

[stevedark · Accepted solution]

Hi @smarties 

As a rule you don't need to join in the load script, rather just let things associate in the data model and then do your calculations in the front end.

Try calling the first load Factor, then loading the second bit as Parts - without the left join and don't do the final resident load.

If you then create a table in the front end with a dimensions of Part and Lot and a calculation using Factor, Price and Adder this should work.

To resolve issues with nulls you may need to use the alt function, i.e. + Alt(Factor,0))

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[smarties]

Thanks @stevedark. I have a solution now following your direction. Appreciated.

[stevedark]

That's good to know. Thanks for updating and letting me know.

Steve