topic Re: Manipulating Data in QlikView in QlikView

Manipulating Data in QlikView

polisetti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Manipulating Data in QlikView

swuehl — Mon, 06 Feb 2017 19:37:16 GMT

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?

Re: Manipulating Data in QlikView

maxgro — Mon, 06 Feb 2017 19:41:14 GMT

Is that the result you want?

Re: Manipulating Data in QlikView

vishsaggi — Mon, 06 Feb 2017 19:42:54 GMT

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.

Re: Manipulating Data in QlikView

polisetti — Mon, 06 Feb 2017 19:43:36 GMT

Yes, I need output as shown by you.

Re: Manipulating Data in QlikView

maxgro — Mon, 06 Feb 2017 19:50:19 GMT

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);

Re: Manipulating Data in QlikView

polisetti — Mon, 06 Feb 2017 19:56:49 GMT

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?

Re: Manipulating Data in QlikView

vishsaggi — Mon, 06 Feb 2017 20:20:48 GMT

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