4 Replies Latest reply: Mar 19, 2014 9:38 AM by Gysbert Wassenaar RSS

    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