Qlikview is multiplying files

[#1]

Hey Guys,

So, I’m relatively new to Qlikview and I’m attempting to join numerous files together and perform a referenced calculation. However, when I perform this calculation for more than one file. The amounts are gross different. Below is the load script. Let me know your thoughts.

Equally, I am having issues surrounding creating charts with this data. I appear to different Calculated value if I summarise the data by country code versus buc. Clearly, it should be the same total calculated value, right?

Table_2:
LOAD
CATEGORY_NAME,
CountryOfRecovery,
CountryOfRecovery & CATEGORY_NAME as [Key Indicator],
CountryOfRecovery & CATEGORY_NAME as [Key Indicator Map],
Rate as [Expect VAT Rate],
RecoverablePercentage,
[Multiplier + Recovery Percentage]  as [Multiplier]

FROM
Restrict
(ooxml, embedded labels, header is 1 lines, table is [Logic table]);

TableMapping:
MappingLOAD
[Key Indicator],
[Multiplier]
Resident Table_2;

////Beligum load
Table_1:
LOAD [SR NO],
[TRANSACTION DATE   ],
BUC,
[E TRAN ISO COUNTRY CODE] as [Country Code],
[GROSS AMOUNT] as [Base],
[RECEIPT TAX AMOUNT],
[GROSS RECLAIM AMOUNT],
[E TRAN ISO COUNTRY CODE] & [CATEGORY NAME]as [Key Indicator],
[File Name]
FROM
Restricted


join (Table_1)
LOAD [SR NO],
[TRANSACTION DATE   ],
BUC,
[E TRAN ISO COUNTRY CODE] as [Country Code],
[GROSS AMOUNT] as [Base],
[RECEIPT TAX AMOUNT],
[GROSS RECLAIM AMOUNT],
[E TRAN ISO COUNTRY CODE] & [CATEGORY NAME]as [Key Indicator],
[File Name]
FROM
Restricted

TestApplyMap:
Load
[Key Indicator],
[Base]*ApplyMap('TableMapping',[Key Indicator],'missing') as VATEstimated
Resident Table_1;



Drop Table Table_2;

[oknotsen]

So new to QlikView that you are posting it under "New to Qlik Sense"? But no worries; the load script and calculation should be the same.

I am having a hard time understand your specific code. Mainly because I am missing the actual calculation you are trying to do and we have no data to go with your example. Could you clarify this a bit?

What are you trying to achieve with this code? What do the source tables look like? What is the calculation you are doing in the end?

[#3]

Essentially, I'm trying to join two tables and then multiply the base amount with reference table for the whole file

[oknotsen]

Still not 100% sure I understand you, so trying a more general example. How about a code like this?

TableA:

Load

KeyField,

FieldA,

FieldB,

FieldX

From SourceA;

TableB:

join(TableA)

KeyField,

FieldA,

FieldB,

FieldX

From SourceB;

TableReference:

mapping

FieldX

FieldY

from SourceZ;

Load

applymap('TableReference', FieldX, 'unknown') * FieldA as ResultA,

applymap('TableReference', FieldX, 'unknown') * FieldB as ResultB

resident TableA;

drop table TableA;