Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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:
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
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.
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
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
Hi Avin,
I don't understand your question re the attachment. Can you download the attachment from my post?
-Rob
Hi Rob,
I could not find the attachment in your post. Could I kindly request to attach it again please.
Thanks again.
Thanks,
Avin
I'm not sure why you can't see the attachment. But here it is again.
Thanks Rob. I could see the attachment now.