topic Re: Design Multiple tables different KPI's in QlikView

Design Multiple tables different KPI's

Wed, 15 Jul 2015 01:09:17 GMT

Hi,

We got around 5 tables, each table has common dimensions like Transactionid, Country, State.But the KPI's are different for each of these tables.

Example:

Retail:

TransactionId Country State Soap_Sales

123 US S1 100

456 US S2 200

Electronics:

TransactionId Country State Computer_Sales

278 US S3 2000

789 US S4 4000

The values of country/state might be same for some transactions..For now there is no requirement to show a chart with KPI's from both tables..Means no requirement to show both Soap_Sales and Computer_Sales in single chart.

Hence,one sheet to contain Retail information and another sheet to contain Electronics information in Qlikview.

Could I check the best design process:

1. Should these tables need to be concatenated? (or)

2. Have it separate tables and may be rename column values so that to avoid join keys/synthetic keys

The challenge here is: Later if we need to include one more new table say: Medicines, which design approach is best to reduce enhancement work in terms of Data model and Dashboard..

Each time I get a new table(say ex: Medicines) seems need to enhance the script and again build a NEW Sheet(ex for Medicines) in dashboard by re creating all the visualizations that has been created in previous sheets for retail/Electronics.

Is there a better approach to handle this from re-usability perspective.

Thanks,

Avin

Re: Design Multiple tables different KPI's

Nicole-Smith — Wed, 15 Jul 2015 02:09:53 GMT

I would concatenate the tables together and add a "Type" column. So for the data listed above, your table (after concatenation) would look like this:

TransactionId	Country	State	Sales	Type
123	US	S1	100	Retail
456	US	S2	200	Retail
278	US	S3	2000	Electronics
789	US	S4	4000	Electronics
189	US	S5	50	Medicines

This will allow you to:

Add more data sets as necessary
Use the same list boxes for all of your dimensions (Country, State, etc) so that they don't have to select a Country in one field on the Retail tab then the same Country again in a different field on the Electronics tab
Use any type of sales on the same chart--I know it isn't your requirement now, but it could change in the future
Use the same tab for all of the data--you could build one sheet and allow the user to select in a list box the field "Type" which would show them what they want to see

Re: Design Multiple tables different KPI's

rwunderlich — Wed, 15 Jul 2015 02:28:59 GMT

I would go with Nicole's suggestion. As an extension to the idea, you could place each sheet (that represents a different Type) in a different alternate state. Put a listbox on the sheet for Type, select a value, set "Always one selected" and then hide the listbox. This would allow you to clone a sheet, changing only the Sheet state name and the listbox selection.

See attached example.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Design Multiple tables different KPI's

jagan — Wed, 15 Jul 2015 03:48:06 GMT

Hi,

You can concatenate like below

Data:

LOAD

TransactionId, Country, State, Soap_Sales AS Sales,

'Retail' AS Category,

FROM RetailData;

Concatenate(Data):

LOAD

TransactionId, Country, State, Computer_Sales AS Sales,

'Electronic' AS Category,

FROM ElectronicsData;

Now you can provide Category as filter and then use below expression

Sum(Sales)

If you want to get Retail Sales then use below expression

Sum({<Category={'Retail'}>}Sales)

Hope this helps you.

Regards,

Jagan.

Re: Design Multiple tables different KPI's

Wed, 15 Jul 2015 04:47:45 GMT

Thanks Nicole, Rob and Jagan. Basically in addition to Sales, I also got some different Projection KPI's in each of these tables.

Example:

In real, this Projection KPI's are calculated by some business logic at source side. What we get is next to Sales column, a new Single column Projection which has all these values. A flag is also provided to know whether this Projection is 25%up or 10%down etc...

Retail:

TransactionId Country State Soap_Sales Projection Flag

123 US S1 100 130 25%_Sales_up

123 US S1 100 90 15%_Sales_down

456 US S2 200 225 15%_Sales_up

Electronics:

TransactionId Country State Computer_Sales Projection Flag

278 US S3 2000 1900 30%_Sales_down

278 US S3 2000 2200 30%_Sales_up

789 US S4 4000 3900 10%_Sales_down

As these projection KPI's will duplicate actual transaction_id(because a Transaction id can have multiple projections, where by actual sales would be duplicated), hence I am using Generic Load to convert only these Projection KPI's from Rows to Columns, so that I will have NEW columns like 25%_Sales_up, 10%_Sales_down etc..

Will proceed with concatenation as per the suggestion. Now as we concatenate tables with different columns, there would be NULLS for non relevant column(Projection KPI) for that specific table, but I think that would not be a problem. Then use Alternate State Sheet wise, as Rob mentioned, to show data respectively.

Please let me know if I would need to correct anything as mentioned above.

Thanks again for the great suggestions and help.

Thanks,

Avin

Re: Design Multiple tables different KPI's

Wed, 15 Jul 2015 08:08:53 GMT

Hi Rob,

Could you help to attach the example. I was trying to replicate the Alternative state idea that you were saying but got stuck in mid. Would be great to see the example. Thanks again.

Thanks,

Avin

Re: Design Multiple tables different KPI's

rwunderlich — Wed, 15 Jul 2015 21:40:59 GMT

Hi Avin,

I don't understand your question re the attachment. Can you download the attachment from my post?

-Rob

Re: Design Multiple tables different KPI's

Sat, 18 Jul 2015 03:30:27 GMT

Hi Rob,

I could not find the attachment in your post. Could I kindly request to attach it again please.

Thanks again.

Thanks,

Avin

Re: Design Multiple tables different KPI's

rwunderlich — Sun, 19 Jul 2015 17:30:24 GMT

I'm not sure why you can't see the attachment. But here it is again.

Re: Design Multiple tables different KPI's

Wed, 22 Jul 2015 02:50:11 GMT

Thanks Rob. I could see the attachment now.