Additional fields in load script causes data integrity issue

[Anonymous]

Hello,

I'm having a bit of trouble in one of my load scripts.

I have 2 if statements that I'm using to calculate an availability metric. I'm doing this by partitioning on Resource and Period to get a count of records and dividing that count by the Availability value.

When i define the fields I intend to use it returns an incorrect result, however, when I use the * notation to select all relevant fields it returns the correct result.

If I load with my script as such:

Fact_Revenue:

LOAD

if(len(trim(#countResourcePeriodRecords_Temp))>0,#countResourcePeriodRecords_Temp,1) as #countResourcePeriodRecords,

if(len(trim(Fact_Resource_Availability.#AvailableHours_Temp))>0,Fact_Resource_Availability.#AvailableHours_Temp,

if([Fact_Revenue_Employee.%EmployeeFlag] = 1,([Fact_Revenue_Employee.DefaultAvailability] * 160),(.875 * 160)))

/

if(len(trim(#countResourcePeriodRecords_Temp))>0,#countResourcePeriodRecords_Temp,1) as #AvailableHours,

[Fact_Revenue_Employee.#Employee Salary] as [#Employee Salary],

Fact_Revenue_Employee.%EmployeeFlag as %EmployeeFlag,

Fact_Revenue_Employee.DefaultAvailability as #DefaultAvailableHours ,

[Fact_Revenue_Temp.#Est Fees] as [#Est Fees],

[Fact_Revenue_Temp.#Est Hrs] as [#Est Hrs],

Fact_Revenue_Temp.#Expenses as #Expenses,

[Fact_Revenue_Temp.#No of Hrs] as [#No of Hrs],

Fact_Revenue_Temp.#PRA_HoursInMonth as #PRA_HoursInMonth,

Fact_Revenue_Temp.#Revenue as #Revenue,

Fact_Revenue_Temp.#Total as #Total,

[Fact_Revenue_Temp.#Var Fees] as [#Var Fees],

[Fact_Revenue_Temp.#Var Hrs] as [#Var Hrs],

Fact_Revenue_Temp.PRA_Consultant as PRA_Consultant,

Fact_Revenue_Temp.PRA_Email as PRA_Email,

Fact_Revenue_Temp.PRA_Period as PRA_Period,

Fact_Revenue_Temp.PRA_ProjectId as PRA_ProjectId,

Period

Resident Fact_Revenue_Temp;

I get this result:

Alternatively, if my load script is this:

Fact_Revenue:

LOAD

if(len(trim(#countResourcePeriodRecords_Temp))>0,#countResourcePeriodRecords_Temp,1) as #countResourcePeriodRecords,

if(len(trim(Fact_Resource_Availability.#AvailableHours_Temp))>0,Fact_Resource_Availability.#AvailableHours_Temp,

if([Fact_Revenue_Employee.%EmployeeFlag] = 1,([Fact_Revenue_Employee.DefaultAvailability] * 160),(.875 * 160)))

/

if(len(trim(#countResourcePeriodRecords_Temp))>0,#countResourcePeriodRecords_Temp,1) as #AvailableHours,

*

Resident Fact_Revenue_Temp;

I get this result:

I thought it was naming convention or concatenation but I can't find any evidence of that.

Any help would be appreciated!

Thanks,

Channing

[sunny_talwar]

Do you only have a single table in your application? Fact_Revenue or do you have more stuff? Also, do you drop Fact_Revenue_Temp at the end of the script?

On a separate note, what is your chart expression? Do they use fields from single table (if you only have one table in your dashboard, this questions won't make sense?

[Anonymous]

Sunny,

Thank you for your quick response.

I have more tables in my application.

I am dropping the temp table after the load and re-naming of fields.

The straight table has 2 dimensions Resource (Different table) and Period (fact_Revenue)

The expressions are as follows

sum(#countResourcePeriodRecords)

sum(Fact_Revenue.#AvailableHours)

[sunny_talwar · Accepted solution]

What about the joins when you rename vs. when you don't rename? Does the synthetic key field change?

[Anonymous]

Sunny,

It was a long Monday. I wasn't including the key fields in my load script.

Once I did that, the relationships were created, and the values returned correctly.

Your mention of the synthetic key field change got me there though!

Thank you!

Channing