topic Re: Display Count of Distinct Order Numbers in a Column in App Development

Display Count of Distinct Order Numbers in a Column

trevorcrow — Tue, 01 Aug 2023 20:24:47 GMT

Hello Qliksense community! I am hoping someone might be able to assist me with my question. I have a table of orders and their current errors. The way the data is structured there can be multiple error records per order number, resulting in a table that looks like the following:

SOURCE_ORDER_ID	ERROR_MESSAGE
1522222	Missing Address
1874666	Missing Payment Information
1544444	Invalid Data
1253232	Missing Payment Information
1253232	Missing Address
1253232	Missing Name

I'm wanting to display the row number in a column that shows the row count by distinct order number. Below is what I'm wanting to display. Any thoughts on how I can accomplish that?

SOURCE_ORDER_ID	ERROR_MESSAGE	Unique Order Count
1522222	Missing Address	1
1874666	Missing Payment Information	2
1544444	Invalid Data	3
1253232	Missing Payment Information	4
1253232	Missing Address	4
1253232	Missing Name	4

Re: Display Count of Distinct Order Numbers in a Column

BrunPierre — Tue, 01 Aug 2023 20:50:03 GMT

Hi, Create a straight table or pivot table with the following

Dimensions: SOURCE_ORDER_ID and ERROR_MESSAGE

Measure: =Aggr(NODISTINCT RowNo(), SOURCE_ORDER_ID)

Re: Display Count of Distinct Order Numbers in a Column

cristianj23a — Tue, 01 Aug 2023 21:37:01 GMT

Hi, try this code:

myTable:
LOAD SOURCE_ORDER_ID,
ERROR_MESSAGE,
Autonumber(SOURCE_ORDER_ID) as TempID INLINE [
SOURCE_ORDER_ID, ERROR_MESSAGE
1522222, Missing Address
1874666, Missing Payment Information
1544444, Invalid Data
1253232, Missing Payment Information
1253232, Missing Address
1253232, Missing Name
];

TempTable:
LOAD *,
If(Peek('TempID') = TempID, Peek('Unique Order Count'), If(IsNull(Peek('Unique Order Count')), 1, Peek('Unique Order Count')+1)) as 'Unique Order Count'
Resident myTable
Order by TempID;

Drop Table myTable;
DROP Fields TempID from TempTable;

RENAME Table TempTable to myTable;

Regarts.

Re: Display Count of Distinct Order Numbers in a Column

vinieme12 — Fri, 04 Aug 2023 06:16:13 GMT

use AutoNumber()

Load

SOURCE_ORDER_ID

,ERROR_MESSAGE

,Autonumber(SOURCE_ORDER_ID) as ID

Re: Display Count of Distinct Order Numbers in a Column

trevorcrow — Mon, 07 Aug 2023 16:46:53 GMT

This worked perfectly and was very simple to add into my load statement. Thanks so much everyone!