Joining problem.

[#1]

Hi Guys,

I'm simply trying to join the following tables:

Payments:

LOAD Date(Date, 'YYYY/MM/DD') as Date,

     Description,

     Amount,

     Balance,

     F9 as AccruedBankCharges

FROM

[Data\Aug-Sep.xlsx]

(ooxml, embedded labels, table is [page 2]);

Payments1:

Load

    Date,

    if(IsText(Amount)=-1 and Right(Amount, 2)='Cr', Num(PurgeChar(Amount, ' Cr')), Sum(Num(PurgeChar(Amount, ' ')))*-1) as Amount

Resident Payments

Group By Date,Amount;

I would like to replace the Amount field in Payments with the new Amount field in Payments1

Is there a way of joining these two tables?

Kind Regards

[MK_QSL · Accepted solution]

Payments:

LOAD Date(Date, 'YYYY/MM/DD') as Date,

     Description,

     Amount,

     Balance,

     F9 as AccruedBankCharges

FROM

[Data\Aug-Sep.xlsx]

(ooxml, embedded labels, table is [page 2]);

Payments1:

Mapping Load

    Date,

    if(IsText(Amount)=-1 and Right(Amount, 2)='Cr', Num(PurgeChar(Amount, ' Cr')), Sum(Num(PurgeChar(Amount, ' ')))*-1) as Amount

Resident Payments

Group By Date,Amount;

NoConcatenate

Final:

Load Date, Description, Applymap('Payment1',Date,Null()) as Amount, Balance, AccruedBankCharges Resident Payments;

Drop Table Payments;

[#3]

Hi Manish,

Thank you for your reply, this did work, unfortunately because the dates are not unique, the Amount values apply incorrectly to the dates. Any other way?

Kind Regards

[MK_QSL]

Payments:

Load *, Date & '|' & Description as %Key;

LOAD Date(Date, 'YYYY/MM/DD') as Date,

     Description,

     Amount,

     Balance,

     F9 as AccruedBankCharges

FROM

[Data\Aug-Sep.xlsx]

(ooxml, embedded labels, table is [page 2]);

Payments1:

Mapping Load

    %Key,

    if(IsText(Amount)=-1 and Right(Amount, 2)='Cr', Num(PurgeChar(Amount, ' Cr')), Sum(Num(PurgeChar(Amount, ' ')))*-1) as Amount

Resident Payments

Group By %Key,Amount;

NoConcatenate

Final:

Load Date, Description, Applymap('Payment1',%Key,Null()) as Amount, Balance, AccruedBankCharges Resident Payments;

Drop Table Payments;

Drop Field %Key;

[MarcoWedel]

Hi,

maybe also possible:

Payments:
LOAD Date(Date, 'YYYY/MM/DD') as Date,
    Description,
    Amount,
    Balance,
    F9 as AccruedBankCharges
FROM [Data\Aug-Sep.xlsx] (ooxml, embedded labels, table is [page 2]);

Join
LOAD Date,
    Amount,
    If(IsText(Amount) and Right(Amount, 2)='Cr',Num(PurgeChar(Amount,' Cr')),-Sum(Num(PurgeChar(Amount,' ')))) as AmountNew
Resident Payments
Group By Date,Amount;

DROP Field Amount;
RENAME Field AmountNew to Amount;

hope this helps

regards

Marco

[#6]

Hi Manish,

Thanks for your reply.

I didn't use your second solution, but will try it just for interest sake.

I managed to resolve it by adding a RowNo() to the payments table as an ID to link the fields properly, and then use that ID field in the ApplyMap.

Million ways to skin a cat

Thanks again.

[#7]

Hi Marco,

Thanks for your reply, I've managed to sort the problem out differently, but I'd like to try this just for interest sake, and report back.

[MK_QSL]

What I suggest you is to try all ways and find out the lowest time consuming way. This will improve your script running time. (I haven't consider that my solution is the best one.. Just an advise to you!)