topic Add Calculation Field in App Development

Add Calculation Field

apichana — Tue, 19 Jan 2021 08:13:59 GMT

Hi,

I have measure as below to show % Consumption of E20&E85 compare with Total QTY_LITRE ( fuel consumption for all type). I would like to add a new Cal Field on Data Load Editor to show %E20E85 Consumption as a new field. Please advise how to do that.

(sum({<[PRODUCT_NAME]={'GASOHOL E20'}>} [QTY_LITRE])

+sum({<[PRODUCT_NAME]={'GASOHOL E85'}>} [QTY_LITRE]))/sum(QTY_LITRE)

Thank you.

Re: Add Calculation Field

Anil_Babu_Samineni — Thu, 21 Jan 2021 18:59:04 GMT

Perhaps this?

Fact:
Load [PRODUCT_NAME],
Sum([QTY_LITRE]) as [QTY_LITRE]
From Table
Where Match([PRODUCT_NAME], 'GASOHOL E20', 'GASOHOL E85')
Group By [PRODUCT_NAME];

Left Join (Fact)
Load [PRODUCT_NAME],
[QTY_LITRE]/Sum([QTY_LITRE]) as [Total QTY_LITRE]
Resident Fact Group By [PRODUCT_NAME];

Re: Add Calculation Field

apichana — Fri, 22 Jan 2021 02:23:32 GMT

Hi,

Thank so much. I did it as advice but got some error

Fact:
Load [PRODUCT_NAME],
Sum([QTY_LITRE]) as [QTY_LITRE]
FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)
Where Match([PRODUCT_NAME], 'GASOHOL E20', 'GASOHOL E85')
Group By [PRODUCT_NAME];

Left Join (Fact)
Load [PRODUCT_NAME],
[QTY_LITRE]/Sum([QTY_LITRE]) as [Total QTY_LITRE]
Resident Fact Group By [PRODUCT_NAME];

Re: Add Calculation Field

Anil_Babu_Samineni — Fri, 22 Jan 2021 07:01:05 GMT

Are you loading only fact table? If so, For me it is quite Group By issue where as If you are loading from different tables of [PRODUCT_NAME], [QTY_LITRE] it is required to alter the script.

Try like this, Because since there is no aggregate in join table for measure it will throw error as it is require to group non aggregate fields.

Fact:
Load [PRODUCT_NAME],
Sum([QTY_LITRE]) as [QTY_LITRE]
FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)
Where Match([PRODUCT_NAME], 'GASOHOL E20', 'GASOHOL E85')
Group By [PRODUCT_NAME];

Left Join (Fact)
Load [PRODUCT_NAME],
[QTY_LITRE]/Sum([QTY_LITRE]) as [Total QTY_LITRE]
Resident Fact Group By [PRODUCT_NAME], [QTY_LITRE];

Re: Add Calculation Field

apichana — Fri, 22 Jan 2021 07:43:53 GMT

Thank you so much for your reply and advice. I tried your script and there is no error anymore. I got 1 synthetic key. So I rename Product_Name to Product_Name 1 to remove synthetic key. Actually there is other table loaded. I have Fleet_Card table with have several fields as below. So do I need to resident Fleet_Card as well before left join?

[Fleet_Card]:
LOAD
Date([POST_DATE]) AS [POST_DATE-PostDate],
Month([POST_DATE]) AS [POST_MONTH-PostDate],
TRN_DATE as Date,
TRN_TIME,
COMPANY_NAME,
CUSTOMER_ID,
CREDIT_LINE,
DRIVER_NAME,
CARD_NO,
PLATE_NO,
PLATE_NO&'_'&TRN_DATE as PLATE_DATE,
DEPARTMENT,
COSTCENTER,
MER_NO,
TAX_ID,
MERCHANT_NAME_THAI,
MERCHANT_LOCATION,
MERCHANT_ZIP_CODE,
INVOICE_NO,
PRODUCT_NAME,
QTY_LITRE,
QTY_KG,
EXCLUDE_VAT_AMOUNT,
VAT_AMOUNT,
AMT_BAHT,
PRICE_RATE,
ODOMETER,
DISTANCE,
KM_LITRE,
FUEL_LPG_BAHT_KM,
KM_KG,
NGV_BAHT_KM,
BRANCH_NUMBER,
MERCHANT_NAME_VAT_CERTIFICATE_PP20,
ADDRESS_VAT_CERTIFICATE_PP20,
PP20_ZIP_CODE

FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)

Fact:
Load [PRODUCT_NAME] as PRODUCT_NAME1,
Sum([QTY_LITRE]) as [QTY_LITRE]
FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)

Where Match([PRODUCT_NAME], 'GASOHOL E20', 'GASOHOL E85')
Group By [PRODUCT_NAME];

Left Join (Fact)
Load [PRODUCT_NAME1],
[QTY_LITRE]/Sum([QTY_LITRE]) as [Total QTY_LITRE]
Resident Fact Group By [PRODUCT_NAME1], [QTY_LITRE];

Re: Add Calculation Field

Anil_Babu_Samineni — Thu, 28 Jan 2021 16:40:30 GMT

This may fix
[Fleet_Card]:
LOAD
Date([POST_DATE]) AS [POST_DATE-PostDate],
Month([POST_DATE]) AS [POST_MONTH-PostDate],
TRN_DATE as Date,
TRN_TIME,
COMPANY_NAME,
CUSTOMER_ID,
CREDIT_LINE,
DRIVER_NAME,
CARD_NO,
PLATE_NO,
PLATE_NO&'_'&TRN_DATE as PLATE_DATE,
DEPARTMENT,
COSTCENTER,
MER_NO,
TAX_ID,
MERCHANT_NAME_THAI,
MERCHANT_LOCATION,
MERCHANT_ZIP_CODE,
INVOICE_NO,
PRODUCT_NAME,
QTY_LITRE,
QTY_KG,
EXCLUDE_VAT_AMOUNT,
VAT_AMOUNT,
AMT_BAHT,
PRICE_RATE,
ODOMETER,
DISTANCE,
KM_LITRE,
FUEL_LPG_BAHT_KM,
KM_KG,
NGV_BAHT_KM,
BRANCH_NUMBER,
MERCHANT_NAME_VAT_CERTIFICATE_PP20,
ADDRESS_VAT_CERTIFICATE_PP20,
PP20_ZIP_CODE
FROM [lib://FM/Fleet_Card_*.qvd] (qvd);
Join
Fact:
Load [PRODUCT_NAME] ~~as PRODUCT_NAME1~~,
Sum([QTY_LITRE]) as [QTY_LITRE]
FROM [lib://FM/Fleet_Card_*.qvd]
(qvd)
Where Match([PRODUCT_NAME], 'GASOHOL E20', 'GASOHOL E85')
Group By [PRODUCT_NAME];
Left Join (Fact)
Load [PRODUCT_NAME1],
[QTY_LITRE]/Sum([QTY_LITRE]) as [Total QTY_LITRE]
Resident Fact Group By [PRODUCT_NAME1], [QTY_LITRE];

Re: Add Calculation Field

apichana — Fri, 29 Jan 2021 02:00:46 GMT

Hi,

Thanks so much for your advice. I'll try it.

Re: Add Calculation Field

apichana — Fri, 29 Jan 2021 02:01:23 GMT

Hi,

Thanks so much for your advice. I'll try it.