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