Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two files one Excel (Purchase Orders) and one CSV (Purchase Orders History).
Purchase Orders (PO) contains all the created purchasing lines and Purchase Orders History (POH) all the transactions operated on the purchasing lines. Thus, I have more records into POH than into the PO.
PO table fields are: PO_No, PO_Line_No, PO_Subline_No, Creation_Date
POH table fields are: PO_No, PO_Line_No, PO_Subline_No, Transaction Date, Action_Type, Old_Value, New_Value
I want to create a unique table into Qlikview in order to calculate the turn around time between the Creation_Date and the Transaction_Date. I will use a concatenation of PO_No, PO_Line_No and PO_Subline_No in order to have a unique Po_Index.
I have created the following script but don't get the Creation_Date assigned into the concatenated table nor the claculation of TAT which generates an error at this moment:
History: //Transactions table
LOAD [Supplier No] as [Supplier Local Number],
PO_No,
PO_Line_No,
PO_Subline_No,
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
Timestamp,
IF(Len(Timestamp)=8,Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'YYYY/MM/DD'),
Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'))
as Transaction_Date,
Field,
[Old value],
[New value],
[User ID],
Source
FROM
IF(Len([PO Header Pub. Date])=19,Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'),
Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'))
as Creation_Date
// (DayNumberOfYear(Action_Date) - DayNumberOfYear((Creation_Date))) as TAT
FROM
[C:\Users...);
Many thanks in advance for any help.
Best regards.
Idriss
Try This
History: //Transactions table
LOAD
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
From POH;
Concatenate (History)
LOAD
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
From PO;
//Transactions table
Join (History)
LOAD Distinct
[Supplier No] as [Supplier Local Number],
PO_No,
PO_Line_No,
PO_Subline_No,
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
Timestamp,
IF(Len(Timestamp)=8,Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'YYYY/MM/DD'),
Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'))
as Transaction_Date,
Field,
[Old value],
[New value],
[User ID],
Source
FROM
IF(Len([PO Header Pub. Date])=19,Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'),
Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'))
as Creation_Date
(DayNumberOfYear(Action_Date) - DayNumberOfYear(Creation_Date)) as TAT
FROM
[C:\Users...);
Try This
History: //Transactions table
LOAD
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
From POH;
Concatenate (History)
LOAD
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
From PO;
//Transactions table
Join (History)
LOAD Distinct
[Supplier No] as [Supplier Local Number],
PO_No,
PO_Line_No,
PO_Subline_No,
PO_No&PO_Line_No&PO_Subline_No as Po_Index,
Timestamp,
IF(Len(Timestamp)=8,Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'YYYY/MM/DD'),
Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'))
as Transaction_Date,
Field,
[Old value],
[New value],
[User ID],
Source
FROM
IF(Len([PO Header Pub. Date])=19,Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'),
Date(Date#([PO Header Pub. Date],'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY'))
as Creation_Date
(DayNumberOfYear(Action_Date) - DayNumberOfYear(Creation_Date)) as TAT
FROM
[C:\Users...);
Hi Aar,
Thank you, I can see the solution is getting started.
It is working pretty well, except for the transaction_date that is not uploaded; the field is empty.
Whereas for the creation_date, it's working fine.
The TAT is not calculated; but I have put it as a comment until the transaction_date issue is sorted out.
Best regards,
Idriss
Hi Aar,
Sorry I bothered you for nothing; it is a problem of Excel refomatting the timestamp when the csv file is modified.
I will do other tests and let know.
Best regards
Hello Aar,
Your procedure is working fine.
I still have not my TAT calculated but I think that's because my creation_date and transaction_date fields are not available at the moment the TAT calculation request is made.
Anyway thanks a lot for your help.
Best regards.
Idriss