Manipulating Data in QlikView

[polisetti]

Hi Everyone,

We have 2 tables Sales and Population. Both these tables are at different granularities (Ex: In the given example Table 1 - is at Country and State level and Table 2 - is at Country level). Now requirement in report is - I need to show a table with Country and State as a dimension and Metrics as Sales/Lives. Here we don't have Lives metric at State level which will result to show null data.

I need to populate/manipulate Lives number in table 2 to show same number to all the States present in that Country (Ex: INDIA Lives needs to be populates to A and B states as 20000).

Is there any way to handle this at expression level of a chart or Data Model level?

Table 1-Sales:                                                                      

Country	State	Sales
INDIA	A	231
INDIA	B	123
US	A	12
US	B	243

Table 2-Lives:

Country	Lives
INDIA	20000
USA	10000

Thanks in Advance

Polisetti

[swuehl]

With your two tables linked by Country, a chart with the two dimensions and

=Sum(Sales) / Avg(Lives)

as expression should show the desired values, isn't it?

[maxgro]

Is that the result you want?

[vishsaggi]

Try this?

Table2:

Mapping LOAD * INLINE [

Country, Lives

INDIA, 20000

US, 10000

];

TableSales:

LOAD *, ApplyMap('Table2', Country) AS Lives INLINE

[

Country, State, Sales

INDIA, A, 231

INDIA, B, 123

US, A, 12

US, B, 243

];

Using Straight table:

Use Dim as Country and State.

[polisetti]

Yes, I need output as shown by you.

[maxgro]

I just read your data

F1:

LOAD Country,      State,      Sales

FROM [https://community.qlik.com/thread/248880] (html, codepage is 1252, embedded labels, table is @1);

F2:

LOAD if(Country='USA', 'US', Country) as Country,      Lives

FROM [https://community.qlik.com/thread/248880] (html, codepage is 1252, embedded labels, table is @2);

[polisetti]

Thanks for the quick response Nagaraju.

We already tried method of doing ApplyMap() function and the problem is the Sales tables granularity is Zip level. The records will be like INDIA -- A -- Zip 1 --Sales, when we creating a key to map with sales table, duplication was happening as the same key will be present in Sales table with different records at Zip level for a country.

Can we consider only distinct of a key and then populate the Lives in Sales table?

[vishsaggi]

Can you give us more information on your data model? Did you try Stefan's or Massimo's scripts ?