Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is my situation:
A have 2 Excel .CSV ";" seperated files that i extract to a extract.qvd file.
First Excel contains:
ID | Timedate | Price | PostalCode
Second Excel contains:
ID | PostalCode | City | Region
Second Excel table is LEFT JOIN(ing) the First table trough this code:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Customerpostalcode:
LOAD %key_CustomerId,
TimeDate,
Bill,
%key_postalcode
FROM
[$(vExtract)customerpostalcode.qvd]
(qvd);
Postalcode:
LEFT JOIN (Klantpostcodes)
LOAD %key_postalcodeID,
%key_postalcode,
City,
Region
FROM
[$(vExtract)postalcode.qvd]
(qvd);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What i got is that here in Belgium alot of postalcodes have multiple cities and what Qlikview do for example is:
When postal code "8500" has a price of €50 and postal code contains 10 cities than you have €500 euro but that is not the intention however it is possible that a postalcode is in the .CSV file multiple times.
What i have now is that the SUM results in a price that isn't the right one. How can i filter so that there is one price for one postalcode
Everything seems correct, for each customerID you have more bill at different times each one with a postal code;
this is left joined to cities with corrispondent postalcode.
Verify that postal codes in first and second table have the same format and definition ...
Try using a distinct in your sum. Something like sum(distinct price).
Thank you for the reactions.
Not only the total is wrong but also the detail PIVOT table. You can find a screenshot in te starting post above. Like you see there for example i have sell something to a person with postal code 9700 of 457€ but it is showing the 457€ on every city that has this postalcode and SUM then to.
Sorry, I haven't passed my screenshot scrying exam yet. Can you post a sample document that demonstrates the problem?