Merge tables in load script

[#1]

Hello all,

I've attached my script - I'm really struggling with this one.

I have my source data showing call volume by day loaded from a .txt file which in table DAPPLICATIONSTAT.

Field ApplicationID is then decoded in the inline table APPLICATIONID_DECODE to get the market that each ApplicationID relates to.

Finally the daily call forecast for each market is then loaded in via the .xls file CALL_FORECAST

As things currently stand I get a loop.

I'm struggling because the call forecast is calculated for each market per day. But the actual call data relates to the ApplicationID and there is more than one ID for each market.......I'm confused!

I think it's possible to merge DAPPLICATIONSTAT and APPLICATION_ID in the load script and then perhaps create a key e.g. Date_Market which I would then link to CALL_FORECAST, but don't know how to do this.........would it even work?

Any assistance is greatly appreciated as always.

APS

[fernandotoledo]

I understood that the Application ID is a more detailled level of information. If you want to compare forecast vs. actual you should link the tables through a key in the same level of detail.

You can either aggregate the actual per market then compare with the forecast or bring a more detailed level of the forecast , in application level.

If you just merge them both you will duplicate the forecast if a market has two applications.

[#3]

Hi Fernando, thanks for the reply.

You have highlighted the main problem. The forecast is calculated at market level but there is more than one ApplicationID for each market, the result being duplication.

Yes, the intention is to compare actual with forecast at market level.

Does anyone else have any ideas?

[#4]

Hi

can't get your problem. Tables are correctly connected by keys.

Use Market as dimension, sum(CallsAnswered) and sum(Forecast) as expression and the comparison will show?

I think I missed something?

Regards

Juerg

[#5]

Hello,

In my document I have additional data loaded into DAPPLICATIONSTAT - all of which is multiplied becasue of the loop created by my script. This is why I was thinking perhaps merge the tables in the load script and create a key but as has been stated above, the data is at different levels so I don;t think it will work.