topic Re: Data Model Wrong Approach in QlikView

Data Model Wrong Approach

Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts

Please help me in creating Clean and optimized Data Model.

I have Attached the Qvw.

1) I have 2 data sets which i have concatenated.

2) Calculating Min Max after Concatenation of 2 Tables.

3) Many-to-Many Relationship Tables and I have used Left Join between them (Bad Approach).

But this is required because I want All the dates against the Unique ID. Main purpose to calculate Min and Max Date from it.

Can I calculate Min Max before or is there any othe way.

My concern areas here are:

1. Left Join (Many-to-Many)

2. Min-Max

Please help me out.

Can I use Link Model for this.

Re: Data Model Wrong Approach

vikasmahajan — Wed, 01 Jun 2016 05:35:58 GMT

you should try to create a master calendar and link your dates with master calendar and then you can calculate min & max date using set analysis , I think there should not be a problem with left join which you used.

Search for master calender and implement the same into your data module.

Thanks

Vikas

Re: Data Model Wrong Approach

Wed, 01 Jun 2016 07:23:04 GMT

Thanks Vikas for the suggestion.

It would me more helpful if you share Sample Qvw.

Re: Data Model Wrong Approach

vikasmahajan — Wed, 01 Jun 2016 07:26:28 GMT

give couple of minutes i will send qvw.

Vikas

Re: Data Model Wrong Approach

Wed, 01 Jun 2016 07:27:07 GMT

@stalwar1 gwassenaar

Sir, what would be your approach here.

Please help.

Re: Data Model Wrong Approach

Wed, 01 Jun 2016 07:46:26 GMT

Thank You Vikas.

Re: Data Model Wrong Approach

Peter_Cammaert — Wed, 01 Jun 2016 08:01:54 GMT

You can calculate the Min/Max date from two separate tables without concatenating or joining them like this:

TmpMinMax: Load min(T1Date) as TMinDate, max(T1Date) as TMaxDate resident T1;

Concatenate (TmpMinMax) Load min(T2Date) as TMinDate, max(T2Date) as TMaxDate resident T2;

MinMax: Load min(TMinDate) as MinDate, max(TMaxDate) as MaxDate resident TmpMinMax;

Drop table TmpMinMax;

Let vMinDate = num(peek('MinDate'));

Let vMaxDate = num(peek('MaxDate'));

Drop table MinMax;

or even shorter like this:

MinMax: Load min(T1Date) as MinDate, max(T1Date) as MaxDate resident T1;

Concatenate (MinMax) Load min(T2Date) as MinDate, max(T2Date) as MaxDate resident T2;

Let vMinDate = RangeMin(num(peek('MinDate', 0)), num(peek('MinDate', 1)));

Let vMaxDate = RangeMax(num(peek('MaxDate', 0)), num(peek('MaxDate', 1)));

Drop table MinMax;

However, I didn't say not concatenating/joining is the best solution for your other data model problems.

Best,

Peter

Re: Data Model Wrong Approach

Wed, 01 Jun 2016 08:23:26 GMT

Thanks Peter Sir for your reply.

But I need to Group By Min and Max Dates by 3 Parameters.

Re: Data Model Wrong Approach

sasiparupudi1 — Wed, 01 Jun 2016 08:33:42 GMT

Please try like the following

MAIN_TABLE:

LOAD * INLINE [

Unique_ID, Number, Code, Flag

111AAA, 9929, AB, AUDI

111AAA, 9929, CD, AUDI

111BBB, 9929, CD, AUDI

111BBB, 9980, EF, AUDI

112BBB, 8883, EF, AUDI

112BBB, 8886, EF, AUDI

113CCC, 8887, GH, AUDI

];

LEFT JOIN

Load Unique_ID, Date(Min(Origin_Date)) as Origin_Date, Date(min(Start_Date)) as Start_Date, Date(max(End_Date)) as End_Date

INLINE [

Unique_ID, Origin_Date, Start_Date, End_Date

111AAA, 01-05-2016, 01-05-2016, 03-05-2016

111AAA, 02-05-2016, 02-05-2016, 04-05-2016

111AAA, 03-05-2016, 03-05-2016, 05-05-2016

111BBB, 03-05-2016, 04-05-2016, 06-05-2016

111BBB 04-05-2016, 05-05-2016, 07-05-2016

112BBB, 04-05-2016, 06-05-2016, 08-05-2016

112CCC, 05-05-2016, 07-05-2016, 09-05-2016

112DDD, 06-05-2016, 08-05-2016, 10-05-2016

]

Group by Unique_ID;

CONCATENATE(MAIN_TABLE)

LOAD * INLINE [

Unique_ID, Number, Zone, Flag, Code

1111EEEE, 1001, SOUTH, BMW, AB

1111EEEE, 1002, WEST, BMW, BC

1111EEEE, 1003, NORTH, BMW, BC

1111FFFF, 1004, EAST, BMW, CD

1112FFFF, 1005, EAST-1, BMW, CD

1112GGGG, 1006, NORTH-1, BMW, EF

1112GGGG, 1007, NORTH-2, BMW, GH

1112EEEE, 1008, EAST-2, BMW, GH

];

LEFT JOIN

Load Unique_ID, Date(Min(Origin_Date)) as Origin_Date, Date(min(Start_Date)) as Start_Date, Date(max(End_Date)) as End_Date

INLINE [

Unique_ID, Origin_Date, Start_Date, End_Date

1111EEEE, 21-05-2016, 21-05-2016, 24-05-2016

1111EEEE, 22-05-2016, 21-05-2016, 25-05-2016

1111EEEE, 23-05-2016, 22-05-2016, 25-05-2016

1111FFFF, 24-05-2016, 22-05-2016, 26-05-2016

1112FFFF, 25-05-2016, 22-05-2016, 26-05-2016

1112FFFF, 26-05-2016, 23-05-2016, 27-05-2016

1112FFFF, 27-05-2016, 23-05-2016, 28-05-2016

1112GGGG, 28-05-2016, 23-05-2016, 29-05-2016

1112GGGG, 29-05-2016, 24-05-2016, 29-05-2016

1112EEEE, 30-05-2016, 25-05-2016, 30-05-2016

1112FFFF, 31-05-2016, 26-05-2016, 30-05-2016

1112FFFF, 01-06-2016, 26-05-2016, 31-05-2016

]

Group by Unique_ID;

hth

Sasi