topic Re: number records aggregated on dimension in App Development

number records aggregated on dimension

Anonymous — Fri, 17 Jun 2016 09:51:57 GMT

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

Re: number records aggregated on dimension

MK_QSL — Fri, 17 Jun 2016 10:01:16 GMT

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

Re: number records aggregated on dimension

Anonymous — Fri, 17 Jun 2016 10:19:06 GMT

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

MK_QSL — Fri, 17 Jun 2016 10:48:28 GMT

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;

Re: number records aggregated on dimension

Anonymous — Fri, 17 Jun 2016 11:01:02 GMT

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

MK_QSL — Fri, 17 Jun 2016 11:11:42 GMT

Provide full script..

Re: number records aggregated on dimension

Anonymous — Fri, 17 Jun 2016 11:19:39 GMT

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

MK_QSL — Fri, 17 Jun 2016 12:06:22 GMT

You need proper Input

SQL

SELECT

V_INVOICE.ITM_ID_T

, V_INVOICE.INV_ID_T

FROM

V_INVOICE

This looks incomplete...

Re: number records aggregated on dimension

Anonymous — Fri, 17 Jun 2016 12:15:30 GMT

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

Re: number records aggregated on dimension

MK_QSL — Fri, 17 Jun 2016 12:28:38 GMT

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