Problem with calculated variables in script

Thu, 02 Apr 2015 22:05:43 GMT

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;

Re: Problem with calculated variables in script

Gysbert_Wassenaar — Fri, 03 Apr 2015 12:37:33 GMT

the first three variables calculate ok, but the rest just produce zeroes,

Which variables are those? I don't see any variables defined in your script. Or are you talking about field names?

Can you post the .qvf document and the excel file?

Re: Problem with calculated variables in script

Fri, 03 Apr 2015 14:23:34 GMT

Sorry, yes, calculated fields.

topic Re: Problem with calculated variables in script in App Development

Problem with calculated variables in script

Re: Problem with calculated variables in script

Re: Problem with calculated variables in script