Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

number records aggregated on dimension

Hello

I got a table INVOICE and I'm selecting 3 fields from this table: ITM_ID_T (item identifier), INV_ID_T (invoice identifier) and I_VALUE as total value for respective INV_ID_T.
In the data set it may happen 1 ITM_ID_T has associated more than 1 INV_ID_T.

My goal is to get them counted, so I can tell which INV_ID_T was added as the first one.

I will appreciate any help.
Thanks

9 Replies
MK_QSL
MVP
MVP

Input:

LOAD ITM_ID_T,

     INV_ID_T

FROM

[itm on inv.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Input)

Load

  ITM_ID_T,

  COUNT(DISTINCT INV_ID_T) as [total count INV in ITM]

Resident Input

Group By ITM_ID_T;

Left Join (Input)

Load ITM_ID_T, INV_ID_T, AutoNumber(ITM_ID_T & '|' & INV_ID_T, ITM_ID_T) as [running count INV in ITM] Resident Input

Order By ITM_ID_T, INV_ID_T

Anonymous
Not applicable
Author

Hi

Thanks for quick reply. The file I attached it's just an overview. I'm using SQL query to get data.

My current code looks like below:

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE

;

Could you help me adjust it ?

MK_QSL
MVP
MVP

Input:

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE

Left Join (Input)

Load

  V_INVOICE.ITM_ID_T,

  COUNT(DISTINCT V_INVOICE.INV_ID_T) as [total count INV in ITM]

Resident Input

Group By V_INVOICE.ITM_ID_T;

Left Join (Input)

Load V_INVOICE.ITM_ID_T, V_INVOICE.INV_ID_T, AutoNumber(V_INVOICE.ITM_ID_T& '|' & V_INVOICE.INV_ID_T, V_INVOICE.ITM_ID_T) as [running count INV in ITM] Resident Input

Order By V_INVOICE.ITM_ID_T, V_INVOICE.INV_ID_T;

Anonymous
Not applicable
Author

I don't want to bother you too much but I got an error with the 2nd left join "Table not found"

just line above you pasted here I added this:

LIB CONNECT TO 'my_lib_data';


SQL

Input:

do you have any idea how to fix it?

MK_QSL
MVP
MVP

Provide full script..

Anonymous
Not applicable
Author

LIB CONNECT TO 'itb data';

Input:

SQL

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE

Left Join (Input)

Load

  V_INVOICE.ITM_ID_T,

  COUNT(DISTINCT V_INVOICE.INV_ID_T) as [total count INV in ITM]

Resident Input

Group By V_INVOICE.ITM_ID_T;

Left Join (Input)

Load V_INVOICE.ITM_ID_T, V_INVOICE.INV_ID_T, AutoNumber(V_INVOICE.ITM_ID_T& '|' & V_INVOICE.INV_ID_T, V_INVOICE.ITM_ID_T) as [running count INV in ITM] Resident Input

Order By V_INVOICE.ITM_ID_T, V_INVOICE.INV_ID_T;


MK_QSL
MVP
MVP

You need proper Input

SQL

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE



This looks incomplete...

Anonymous
Not applicable
Author

Well, for start I need only these 2 fields and I'm not using the filtering.

MK_QSL
MVP
MVP

Try to use debug of script and let me know where exactly you are getting error