Add Calculation Field

[apichana]

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. 

[Anil_Babu_Samineni]

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];

[apichana]

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];

[Anil_Babu_Samineni · Accepted solution]

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];

[apichana]

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];

[Anil_Babu_Samineni · Accepted solution]

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];

---

[apichana]

Hi, 

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

[apichana]

Hi, 

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