topic Re: Concatenating a table into Single Fact in QlikView

Concatenating a table into Single Fact

Mon, 19 May 2014 10:21:31 GMT

!Hi Experts,

We are creating Single Fact Star Schema Model for our application. While doing so we have concatenated one table to the exiting single fact table. We are able to meet all the requirements from Dashboard perspective except one.

Need your help on the same.

Requirement : Need a STACK Chart for Units and Q_Units at Region_Id

I have attached the Sample file.

Thanks,

Kumar

Re: Concatenating a table into Single Fact

hic — Mon, 19 May 2014 11:25:09 GMT

This is in principle easy:

Add an expression Sum(Q_Units)
Change to "Stacked" on the Style sheet

However, your data has a problem: Your Q_Units are not associated with Region_Id, so the chart does not look very well. In the picture below I have selected all Regions, and then all Q_Units are excluded.

So, you need to fix your data model.

HIC

Re: Concatenating a table into Single Fact

Mon, 19 May 2014 11:51:19 GMT

hi kumar please check the scripting part ,there might be some problem in data linking

Re: Concatenating a table into Single Fact

Mon, 19 May 2014 12:42:49 GMT

Hi HIC & Rao,

Thanks for your reply. If you check the expression in text box i have written, it tells how we can calculate Q_Units at Region level.

='Q_Units : ' &

Sum({1<TERRITORY=P(Territory_Id),DIM_TIME_ID=P(DIM_TIME_ID)>}Q_Units)

Since, Q_Fact has been concatenated not joined.

Individually , I am able to create charts from both units and Q_Units but need to collaborate both to have one STACK Chart.

Thanks,

Kumar

Re: Concatenating a table into Single Fact

Mon, 19 May 2014 13:50:36 GMT

Hi HIC,

Any idea on how to make this Stack Chart.

Details are mentioned in my below thread.

Thanks,

Kumar

Re: Concatenating a table into Single Fact

hic — Mon, 19 May 2014 14:26:47 GMT

It might be possible to create a Set Analysis expression that does this, but I think that that is the wrong path forward. It would lead to an expression that is unmanageable that you will not understand when you look at it in 3 months.

Instead, I think you should fix your data model. Below I have a table box with your data:

You can clearly see that you have a mismatch in your fact table - you have two different territory fields even though they are the same thing. Instead of storing Territory_Id and TERRITORY in two different fields, you should store them in one field in the fact table. With Region in a dimensional table (Territory should be primary key) the chart will work right away with a very simple expression.

Concatenating a fact table is only useful if you have common fields between the two parts.

To get the Territory_Id into the fact table, you can use Applymap (See page 16 in Generic keys).

HIC