Creating dimension for set analysis by combining dimensions from two datasets

[#1]

Hi,

At the moment, I get both the scheduled time arrival time and the actual arrival time from the same file. To calculate the performance (truck arrives on the correct day), I added the following line to my script:

if(stp_scheduled_latest-stp_arrival_date)>=0,1,0) as [On time]

To calculate the number of late shipments, I use the following formula in my table:

=count({<[On time]={0}, FlagA={'A'}, FlagB={'B'}>} DISTINCT Order)

And to calculate the performance, I used this formula:

=count({<[On time]={1}, FlagA={'A'}, FlagB={'B'}>} DISTINCT Order)/count({<FlagA={'A'}, FlagB={'B'}>} DISTINCT Order)

I have flagged two files, because in File A I have the transport data and in file B orderdata, and I only want to see files that are in both data. The problem I have now, is that in stead of using both the scheduled arrival as the actual arrival from the transport file, I have to use the scheduled arrival time from the order file (B) and the actual arrival from the order file (A).  But as a result, I cannot use the If-statement in my script in tabel A anymore, and as a result also not the set analysis.

Anybody any suggestions?

[Gysbert_Wassenaar · Accepted solution]

Perhaps you can use a join to create one table with some data from both files.

Temp:
LOAD * from FileA;

join(Temp)
LOAD OrderID, stp_scheduled_latest as stp_scheduled_latest_B from FileB;

Result:
Load *, if(stp_scheduled_latest_B - stp_arrival_date)>=0,1,0) as [On time]
Resident Temp;

drop Table Temp;

[#3]

Hi Gysbert,

I could do that, but if I combine the data to one table, how do I make sure I only use data that is in both datasources?

Best regards,

Niek

[swuehl]

Niek,

could you detail a bit more how your two tables are structured and linked to each other? Also, a small sample application (best using some small inline tables, so one can modify and reload the script) would help us to help you.

Regards,

Stefan