topic Re: Help with Pivots/Dimensions in QlikView

Help with Pivots/Dimensions

Thu, 24 Jan 2013 23:35:33 GMT

Hi,

I am new to qv & am trying to create a smart pivot.

I have a table with prices of products across stores & states for a given date range. I need to create a pivot table that would highlight products that have different prices across stores in a given state for a given date. Ideally, I need a pivot table that will give me Date, State, #ProductsWithMultiplePrices. On clicking the specific Date/State, it should expand into the list of products with multiple prices.

Consider the below dataset :

ProductPrices:

LOAD * INLINE

[
Date, State, Store, Product, Price
1-Jan-2013, S1, ST11, P1, 101
1-Jan-2013, S1, ST11, P2, 102
1-Jan-2013, S1, ST12, P1, 102
1-Jan-2013, S1, ST12, P2, 102
1-Jan-2013, S2, ST21, P1, 110
1-Jan-2013, S2, ST21, P2, 120
1-Jan-2013, S2, ST21, P3, 130
1-Jan-2013, S2, ST22, P1, 111
1-Jan-2013, S2, ST22, P2, 120
1-Jan-2013, S2, ST23, P1, 110
1-Jan-2013, S2, ST23, P2, 121
];

What I want is the following :

initially it should show me

1-Jan-2013, S1, 1

1-Jan-2013, S2, 2

if I click the second row (or rather the 1), it should expand into :

1-Jan-2013, S2, 2, P1

1-Jan-2013, S2, 2, P2

What's the best way to create this in pivots/charts ?

The actual data set is across a range of dates, all 50 states & multiple stores per state, multiple products per store.

Appreciate any help.

Thanks

Re: Help with Pivots/Dimensions

khadeer — Fri, 25 Jan 2013 04:07:34 GMT

PFA. I am not sure u got correct answer r not, Check that one , let me know in what way u want rather than that.

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:16:07 GMT

Thanks. Teh pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:16:19 GMT

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:16:28 GMT

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:16:46 GMT

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:17:04 GMT

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

Re: Help with Pivots/Dimensions

Fri, 25 Jan 2013 13:22:08 GMT

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

1-Jan, S1, 1 (since there is only one product in S1 that has multiple prices across stores in that state)

1-Jan, S2, 2 (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).