topic Add a unique identifier with nulls in the table in App Development

Add a unique identifier with nulls in the table

smarties — Wed, 26 Apr 2023 15:25:17 GMT

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;

Re: Add a unique identifier with nulls in the table

stevedark — Wed, 26 Apr 2023 15:41:45 GMT

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/

Re: Add a unique identifier with nulls in the table

smarties — Wed, 26 Apr 2023 16:19:33 GMT

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

Re: Add a unique identifier with nulls in the table

stevedark — Wed, 26 Apr 2023 20:42:00 GMT

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

Steve