number records aggregated on dimension

[Anonymous]

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

[MK_QSL]

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]

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]

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]

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]

Provide full script..

[Anonymous]

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]

You need proper Input

SQL

SELECT

  V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

  V_INVOICE

This looks incomplete...

[Anonymous]

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

[MK_QSL]

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