Display Count of Distinct Order Numbers in a Column

[trevorcrow]

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

[BrunPierre]

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)

[cristianj23a]

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.

[vinieme12 · Accepted solution]

use AutoNumber()

Load

SOURCE_ORDER_ID

,ERROR_MESSAGE

,Autonumber(SOURCE_ORDER_ID) as ID

[trevorcrow]

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