Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Problem with expression

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_IDLocationLocation_Details
110Spaingarage
110Spainhouse
110Italygarage
110Italygarage
110Italyhouse
110United Kingdomstreet
154Polandhouse
178Spaingarage

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_IDLocationAmount
110Spain2
110Italy 3
110United Kingdom1

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

Tags (3)
1 Solution

Accepted Solutions
Highlighted
sarvjeet
New Contributor III

Re: Problem with expression

Hi

Dimention : Product_ID, Location

Expression:

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


Output:

Product_IDLocationCount
110Italy2
110Spain2
110United Kingdom1
154Poland1
178Spain1

-Sarvjeet

View solution in original post

5 Replies
Highlighted
anbu1984
Honored Contributor III

Re: Problem with expression

Can you show expected output

Highlighted
sarvjeet
New Contributor III

Re: Problem with expression

Hi

Dimention : Product_ID, Location

Expression:

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


Output:

Product_IDLocationCount
110Italy2
110Spain2
110United Kingdom1
154Poland1
178Spain1

-Sarvjeet

View solution in original post

Highlighted
sarvjeet
New Contributor III

Re: Problem with expression

I think u updated the source table:

🙂

resut for new source is :

Product_IDLocationCount
110Italy3
110Spain2
110United Kingdom1
154Poland1
178Spain1
Highlighted
Not applicable

Re: Problem with expression

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)

Highlighted
sarvjeet
New Contributor III

Re: Problem with expression

WorkAround 🙂

- use straight table

- Configure dimention limit as shown is snapshot attachedresults.png