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