Design Multiple tables different KPI's

[#1]

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

[Nicole-Smith · Accepted solution]

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:

1. Add more data sets as necessary
2. 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
3. Use any type of sales on the same chart--I know it isn't your requirement now, but it could change in the future
4. 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

[rwunderlich]

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

[jagan]

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.

[#5]

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

[#6]

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

[rwunderlich]

Hi Avin,

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

-Rob

[#8]

Hi Rob,

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

Thanks again.

Thanks,

Avin

[rwunderlich]

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

[#10]

Thanks Rob. I could see the attachment now.