topic Re: Use Apply Map? in QlikView

Use Apply Map?

Mon, 03 Aug 2015 21:59:39 GMT

Hi All

Relatively new to Qlikview. From what I can tell - Apply map appears to be the best option for the below scenario but a bit unsure of how it would work. Have looked through and researched numerous examples.

Currently have 2 tables in my Qlikview document

Table1

Org,AcctId,Currency, ContraOrg,ContraAcctId,ContraCurrency

NZ,611023,AUD,AU,811022,AUD

AU,811022,AUD,NZ,611023,AUD

......

Table2

Org,AcctId,Currency,FiscYrPer,Amount

NZ,611023,AUD,201412,-15200

AU,811022,AUD,201412,15200

.....

Ultimately i need to sum off the values of the two amounts to result in a balance Amount of '0'. My approach had been to concatenate the 1st 3 fields of of the first line in table one (NZ-611023-AUD) to retrieve the related Amount in Table 2 ( -15200). I do the same for the next 3 fields on the first line in table 1 (AU-811022-AUD) to retrieve the related amount (which is line number 2 of Table 2) and call this the ContraAmount (15200)

Final Table

Org,AcctId,Currency, ContraOrg,ContraAcctId,ContraCurrency,Amount,ContraAmount

NZ,611023,AUD,AU,811022,AUD,-15200,15200

AU,811022,AUD,NZ,611023,AUD,-15200,15200

....

any guidance on how to achieve the above would be greatly appreciated.

Taf

Re: Use Apply Map?

Anonymous — Mon, 03 Aug 2015 23:47:17 GMT

See if this helps:

MapTable:
MAPPING LOAD DISTINCT
Org&AcctId&Currency as A,
Amount as B
RESIDENT Table2;

Result:
LOAD
Org,AcctId,Currency, ContraOrg,ContraAcctId,ContraCurrency,
applymap('MapTable', Org&AcctId&Currency) as Amount,
applymap('MapTable', ContraOrg&ContraAcctId&contraCurrency) as ContraAmount
RESIDENT Table1;

DROP Tables Table1, Table2;

Re: Use Apply Map?

Tue, 04 Aug 2015 03:33:48 GMT

Thank you for your response Michael. Although the example script you provided did not give me the results i was expecting - it did however help to modify my script slightly to use "MAPPING LOAD DISTINCT" and not just 'MAPPING LOAD'.

Part of my issue was that my apply map function was giving me duplicate records and i could not (still not to be honest ) understand how they were being created. Now with MAPPING DISTINCT I no longer have the duplicate records issue.

Thank you for your help.

Re: Use Apply Map?

Anonymous — Tue, 04 Aug 2015 10:34:59 GMT

Actually, I always use distinct with mapping load, unless it is inline load...

In your case, the reason for duplicate could be field FiscYrPer which is not used in mapping load.

Re: Use Apply Map?

sasiparupudi1 — Tue, 04 Aug 2015 10:44:49 GMT

I think your key Org,AcctId,Currency,(AU-811022-AUD) will not give you an unique key identifier as the second table is also having a date.

Also , you have common fields between your tables and they probably giving you synthetic keys and they must be avoided.

Post your data file so that we could help you out

HTH

Sasi