topic Joining problem. in QlikView

Joining problem.

Mon, 08 Feb 2016 19:07:34 GMT

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

Re: Joining problem.

MK_QSL — Mon, 08 Feb 2016 19:31:15 GMT

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;

Re: Joining problem.

Mon, 08 Feb 2016 19:53:55 GMT

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

Re: Joining problem.

MK_QSL — Mon, 08 Feb 2016 20:04:36 GMT

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;

Re: Joining problem.

MarcoWedel — Mon, 08 Feb 2016 23:27:33 GMT

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

Re: Joining problem.

Thu, 11 Feb 2016 07:25:46 GMT

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.

Re: Joining problem.

Thu, 11 Feb 2016 07:27:07 GMT

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.

Re: Joining problem.

MK_QSL — Thu, 11 Feb 2016 08:51:14 GMT

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!)