I am trying to add some calculated variables to a table I am inputting from an external file based on a second table which is not linked to the first, using the FieldValue. The secondary table has eight rows, each of which should produce new calculated fields in the primary table. I am doing the calculation in stages to keep the code from getting unwieldy. This means creating a new table for each stage, reading
I am having two problems:
a) the first three variables calculate ok, but the rest just produce zeroes,
b) the script goes continues to load the next two stages, but then gives a "Field not found" error in relation to one of the fields (TC1) defined at a previous stage.
The script is copied below.
Thanks!
NewTariffs:
LOAD * INLINE [
Tariffs, Monthly Subscription, Overage Charge per MB, MB included
"A", 0.40, 0.5, 1
"B", 0.80, 0.3, 2
"C", 2.05, 0.3, 5
"D", 3.40, 0.3, 10
"E", 4.40, 0.3, 50
"F", 5.15, 0.3, 100
"G", 3.50, 0.3, 1024
"TBA",9999.00, 1, 1025
];
Users:
LOAD
MSISDN,
"Invoice Date",
Username,
"Home usage (Kb)",
MRC,
"Usage charges - home",
"Usage charges - roaming",
"Current package cost",
FieldValue('Monthly Subscription', 1) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 1)) * FieldValue('Overage Charge per MB', 1) as TC1,
FieldValue('Monthly Subscription', 2) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 2)) * FieldValue('Overage Charge per MB', 2) as TC2,
FieldValue('Monthly Subscription', 3) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 3)) * FieldValue('Overage Charge per MB', 3) as TC3,
FieldValue('Monthly Subscription', 4) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 4)) * FieldValue('Overage Charge per MB', 4) as TC4,
FieldValue('Monthly Subscription', 5) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 5)) * FieldValue('Overage Charge per MB', 5) as TC5,
FieldValue('Monthly Subscription', 6) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 6)) * FieldValue('Overage Charge per MB', 6) as TC6,
FieldValue('Monthly Subscription', 7) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 7)) * FieldValue('Overage Charge per MB', 7) as TC7,
FieldValue('Monthly Subscription', 😎 + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 8)) * FieldValue('Overage Charge per MB', 😎 as TC8
FROM [lib://Data for Checker/Usage extract.xlsx]
(ooxml, embedded labels, table is CH29_20150319_115647);
Sums1:
LOAD MSISDN,
sum(TC1) as TC1a,
sum(TC2) as TC2a,
sum(TC3) as TC3a,
sum(TC4) as TC4a,
sum(TC5) as TC5a,
sum(TC6) as TC6a,
sum(TC7) as TC7a,
sum(TC8) as TC8a
resident Users group by MSISDN;
Sums2:
Load MSISDN,
RangeMin(TC1a, TC2a, TC3a ) as Cheapest
Resident Sums1;
Sums3:
LOAD MSISDN, TC1a, TC2a, TC3a,
pick( match(Cheapest, TC1a, TC2a, TC3a) , FieldValue('Tariffs', 1), FieldValue('Tariffs', 2), FieldValue('Tariffs', 3)) as Best
Resident Sums2;
