Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Price showing multiple times how to filter

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

Tags (2)
4 Replies
alexandros17
Not applicable

Re: Price showing multiple times how to filter

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 ...

Gysbert_Wassenaar
Not applicable

Re: Price showing multiple times how to filter

Try using a distinct in your sum. Something like sum(distinct price).


talk is cheap, supply exceeds demand
Not applicable

Re: Price showing multiple times how to filter

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.

Gysbert_Wassenaar
Not applicable

Re: Price showing multiple times how to filter

Sorry, I haven't passed my screenshot scrying exam yet. Can you post a sample document that demonstrates the problem?


talk is cheap, supply exceeds demand