Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kamil_stachowic
New Contributor

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

Re: number records aggregated on dimension

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

kamil_stachowic
New Contributor

Re: number records aggregated on dimension

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 ?

Re: number records aggregated on dimension

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;

kamil_stachowic
New Contributor

Re: number records aggregated on dimension

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?

Re: number records aggregated on dimension

Provide full script..

kamil_stachowic
New Contributor

Re: number records aggregated on dimension

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;


Re: number records aggregated on dimension

You need proper Input

SQL

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE



This looks incomplete...

kamil_stachowic
New Contributor

Re: number records aggregated on dimension

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

Re: number records aggregated on dimension

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