How to merge tables without the same number of lines

[#1]

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

[aarkay29 · Accepted solution]

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...);

[#3]

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

[#4]

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

[#5]

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