Count in Load Sciprt

kylealpaugh — Fri, 22 Nov 2019 15:57:58 GMT

i am having a script error on the end of my script when i try to aggregate i can not figure out what is wrong with the script it run well expert for the count at the end any help would be greatly appreciated

[T188T]:
Mapping Load
// Key fields:
[KONDA],
[VTEXT] as [Price Group Descr.]
from $(vSources)T188T.qvd (qvd);

// consolidate the 3 tables of condition price :
[PRICE_COND]:
Load
VKORG as comp_price,
KUNNR as custo_price,
MATNR as mat_price,
KNUMH as cond_price,
DATBI as Valid_to_price,
DATAB as Valid_from_price,
'Customer / Material' as option
from $(vSources)A05.QVD (qvd)
where DATBI > date(today()) and DATAB <= date(today())
;

//added
concatenate(PRICE_COND)

Load
VKORG as comp_price,
KUNNR as custo_price,
MATNR as mat_price,
KNUMH as cond_price,
DATBI as Valid_to_price,
DATAB as Valid_from_price,
'ALL' as option
from $(vSources)A05.QVD (qvd)

;
//endadd
concatenate(PRICE_COND)
load
VKORG as comp_price,
KONDA as custo_grp_price,
ApplyMap('T188T',KONDA) as [Price Group Descr.],
MATNR as mat_price,
KNUMH as cond_price,
DATBI as Valid_to_price,
DATAB as Valid_from_price,
'Price Group / Material' as option
from $(vSources)A32.QVD (qvd)
where DATBI > date(today()) and DATAB <= date(today())
;

concatenate(PRICE_COND)
load
VKORG as comp_price,
MATNR as mat_price,
KNUMH as cond_price,
DATBI as Valid_to_price,
DATAB as Valid_from_price,
'Material with release status' as option
from $(vSources)A304.QVD (qvd)
where DATBI > date(today()) and DATAB <= date(today())
;

//link the condition price table with the pricing table (konp "normal price"/ konm "scale price")
NoConcatenate
PRICE_tmp:
load
comp_price,
custo_price,
custo_grp_price,
[Price Group Descr.],
mat_price,
cond_price,
Valid_to_price,
Valid_from_price,

[custo_grp_price]& '-' &[comp_price] as Pr_Group_Comp,
//[comp_price]& '-' &[mat_price] as Company_Material_Pric,
option
resident PRICE_COND;

left join(PRICE_tmp)
load
KNUMH as cond_price,
if(KZBZG = 'C', ' ', KBETR) as amount_price,
KPEIN as per_unit,
KMEIN as unit_of_measure,
KONWA as currency_price
from $(vSources)KONP.QVD (qvd);

left join(PRICE_tmp)
load
KNUMH as cond_price,
KSTBM as scale_price,
KBETR as amount_scale_price
from $(vSources)KONM.QVD (qvd);

drop table PRICE_COND;

//Add the customer of price group
NoConcatenate
PRICE_tmp2:
load *
resident PRICE_tmp;

/*join(PRICE_tmp2)
load Pr_Group_Comp,
[KUNNR] as custo_price_grp
resident KNVV;
*/
//drop table KNVV;
drop table PRICE_tmp;

//rework the table
NoConcatenate
PRICE_tmp3:
load
//if (isnull(custo_price_grp),[comp_price]& '-' &[mat_price]& '-' & custo_price, [comp_price]& '-' &[mat_price]& '-' & custo_price_grp) as %Company_Material_Pric,
[comp_price]& '-' &[mat_price]& '-' & custo_price as %Company_Material_Pric,
comp_price,
//if (isnull(custo_price_grp), custo_price, custo_price_grp) as custo_price,
custo_price,
custo_grp_price,
[Price Group Descr.],
mat_price,
cond_price,
Valid_to_price,
Valid_from_price,

//Company_Material_Pric,
option,
if (amount_price = ' ', amount_scale_price, amount_price) as amount_price,
per_unit,
unit_of_measure,
currency_price,
if (isnull(scale_price), '1', scale_price) as scale_price
resident PRICE_tmp2
where not isnull(mat_price);

left join(PRICE_tmp3)
Load
KUNNR as custo_price,
[NAME1] as [Cust_price_Name],
KUNNR & ' - ' & NAME1 as [customer code & name price],
[KONZS] as [Corporate Group_Price]
from $(vSources)KNA1.qvd (qvd);

left join(PRICE_tmp3)
LOAD
MATNR as [mat_price],
VKORG as [comp_price],
KUNNR as [custo_price],
KDMAT as [Customer Part Price]
FROM
$(vSourcesShared)KNMT.QVD
(qvd);

left join(PRICE_tmp3)
Load
[KUNNR] as [custo_price],
[VKORG] as [comp_price],
[KONDA] as custo_grp_price

FROM $(vSources)KNVV.QVD (qvd);

[PRICE]:
Load *,
Count(scale_price) as count_scale_price

resident PRICE_tmp3
group by custo_price, mat_price,comp_price ;

drop table PRICE_tmp2;
drop table PRICE_tmp3;

STORE [PRICE] INTO $(vDirectoryPath)PRICE.QVD;

Re: Count in Load Sciprt

Stoyan_Terziev — Fri, 22 Nov 2019 16:25:43 GMT

Hi,

Here you are loading ALL fields from the PRICE_tmp3 table, but grouping by only few of them.

[PRICE]:
Load *,
Count(scale_price) as count_scale_price

resident PRICE_tmp3
group by custo_price, mat_price,comp_price ;

You need to either group by all the fields (including the ones you're joining), or LOAD just the ones you are grouping by like so:

[PRICE]:
Load custo_price, mat_price,comp_price,
Count(scale_price) as count_scale_price

resident PRICE_tmp3
group by custo_price, mat_price,comp_price ;

Example for missing fields in the Group By clause: [Customer Part Price], custo_grp_price

I hope that helps!

Kind regards,

S.T.

topic Count in Load Sciprt in App Development

Count in Load Sciprt

Re: Count in Load Sciprt