topic Re: data model suggestion in QlikView

data model suggestion

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have 2 fact table shown below. Both have same structure.

The problem is, I will be placing both the reports in separate sheets, so the dimensions- ProductName and Location should have the appropriate values of that Report, so users don't see additional values in these dimensions.

Please suggest, the best way to do the data modelling for my requirement.

Report1:

LOAD *

INLINE [

ProductName, MonthYear, Location,Sales

AA, Jan-17, US,100

AA, Feb-17, UK,200

BB, Jan-17, DE,200

];

Report2:

LOAD *

INLINE [

ProductName, MonthYear, Location,Sales

AA, Mar-17, US,100

KK, Feb-17, JP,200

MM, Jan-17, DE,300

];

Please note that the real table has lot many fields and 2nd table is a new one i am going to add to existing model. So i am worried that concatenation and data flag approach will force me to change all the expression in the existing objects.

Thanks!

Re: data model suggestion

arvind_patil — Mon, 11 Sep 2017 09:08:23 GMT

Dear Suraj,

Please find the attachment:

Code:

Report1:

LOAD *

INLINE [

ProductName, MonthYear, Location,Sales

AA, Jan-17, US,100

AA, Feb-17, UK,200

BB, Jan-17, DE,200

];

NoConcatenate

Report2:

LOAD *

INLINE [

ProductName, MonthYear, Location,Sales

AA, Mar-17, US,100

KK, Feb-17, JP,200

MM, Jan-17, DE,300

];

FinalReport1:

Load

ProductName, MonthYear, Location,Sales,

'Report 1'as Flag

Resident Report1;

DROP Table Report1;

Concatenate

FinalReport2:

Load

ProductName, MonthYear, Location,Sales,

'Report 2'as Flag

Resident Report2;

DROP Table Report2;

Thanks,

Arvind Patil

Re: data model suggestion

jagan — Mon, 11 Sep 2017 09:09:30 GMT

Hi Suraj,

make two tables as island in your datamodel by using Qualify. And use different alternate states in both the sheets, this way you can easily implement this.

Regards,

Jagan.

Re: data model suggestion

qliksus — Mon, 11 Sep 2017 09:26:21 GMT

If you don't want to change expression then have a flag created in both the tables and have the flag selected in the respective sheets i.e Sheet1 will have the value Flag = 1 selected and sheet2 would have Flag = 2 selected

Report1:

LOAD * ,'1' as Flag INLINE [

ProductName, MonthYear, Location,Sales

AA, Jan-17, US,100

AA, Feb-17, UK,200

BB, Jan-17, DE,200

];

Report2:

LOAD * ,'2' as Flag INLINE [

ProductName, MonthYear, Location,Sales

AA, Mar-17, US,100

KK, Feb-17, JP,200

MM, Jan-17, DE,300

];

Re: data model suggestion

surajap123 — Mon, 11 Sep 2017 09:26:58 GMT

Thanks for your time. I have not interested in the flag approach, as it require me to do the changes in all existing objects in the dashboard.

Re: data model suggestion

surajap123 — Mon, 11 Sep 2017 09:30:00 GMT

Thanks Jagan. Users still want to filter the data based on the dimensions that are common. Suppose if user select country- JP, then switch to other sheet to see what happened in Report-1(which should break the chart, which is ok as no data available).

The major issue is, i don't want dimensions(country,location) in the listbox to contain all the values belonging to both reports.

Re: data model suggestion

jagan — Mon, 11 Sep 2017 09:44:51 GMT

This will add more complexity you have to derive multiple fields for country and location and you have to use triggers for this.

Regards,

Jagan.

Re: data model suggestion

surajap123 — Mon, 11 Sep 2017 09:48:11 GMT

ok. Can i go with link table.

Re: data model suggestion

jagan — Mon, 11 Sep 2017 09:57:57 GMT

I think it is the same whether you concatenate or use link table, if you want to carry the selections you have to concatenate two data sources, if you concatenate you will get additional values..

Regards,

jagan.

Re: data model suggestion

surajap123 — Mon, 11 Sep 2017 10:02:03 GMT

I get it. Thanks for the clarification.