topic Re: Problem with expression in QlikView

Problem with expression

Tue, 10 Jun 2014 12:40:08 GMT

Hi, again I need your help I spent 3 days on solving this and still I can't figure it out

This is fragment of my table, my source of data:

Product_ID	Location	Location_Details
110	Spain	garage
110	Spain	house
110	Italy	garage
110	Italy	garage
110	Italy	house
110	United Kingdom	street
154	Poland	house
178	Spain	garage

And here is my problem :
I need to create a new table, in which I'll have a Product ID, Location, and the amount of Product_ID in particular Location but all of this have to fullfilled following condition :

I'm looking for a Product_ID which is divided into parts - by this I mean that :
1. Particular Product_ID is in different locations - then I know that some part of my product is in Spain and some in Italy so I can assume that it is divided

2. Particular Product_ID is in the same location but in different Location_Details - now all of parts of my Product can be in Spain but in different Location_Details so it is divided.

Expected Output :

Product_ID	Location	Amount
110	Spain	2
110	Italy	3
110	United Kingdom	1

After 3 days my brain hurts when i'm looking at this table

I feel that im close but still my pivot table shows some null values when it is expanded ...

I hope some genius qlikview users will be able to solve this

Re: Problem with expression

anbu1984 — Tue, 10 Jun 2014 12:45:20 GMT

Can you show expected output

Re: Problem with expression

sarvjeet — Tue, 10 Jun 2014 12:59:53 GMT

Dimention : Product_ID, Location

Expression:

Sum(AGGR(Count(Product_ID), Product_ID,Location,Location_Details))

Output:

Product_ID	Location	Count
110	Italy	2
110	Spain	2
110	United Kingdom	1
154	Poland	1
178	Spain	1

-Sarvjeet

Re: Problem with expression

sarvjeet — Tue, 10 Jun 2014 13:04:58 GMT

I think u updated the source table:

🙂

resut for new source is :

Product_ID	Location	Count
110	Italy	3
110	Spain	2
110	United Kingdom	1
154	Poland	1
178	Spain	1

Re: Problem with expression

Tue, 10 Jun 2014 13:18:52 GMT

I think this is very close to the correct answer but I have to filter this result table in that way that i won't have product_id which according to my conditions (1. and 2. mentioned above) is not divded into parts. So i'll have just 3 first rows from your table (without 154, 178)

Re: Problem with expression

sarvjeet — Tue, 10 Jun 2014 13:38:19 GMT

WorkAround 🙂

- use straight table

- Configure dimention limit as shown is snapshot attached