Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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
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