Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Design Multiple tables different KPI's

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

1 Solution

Accepted Solutions
Nicole-Smith

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:

TransactionIdCountryStateSalesType
123USS1100Retail
456USS2200Retail
278USS32000Electronics
789USS44000Electronics
189USS550Medicines

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

View solution in original post

9 Replies
Nicole-Smith

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:

TransactionIdCountryStateSalesType
123USS1100Retail
456USS2200Retail
278USS32000Electronics
789USS44000Electronics
189USS550Medicines

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
Partner
Partner

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Thanks Rob. I could see the attachment now.