Data Model Wrong Approach

[#1]

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.

[vikasmahajan]

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

[#3]

Thanks Vikas for the suggestion.

It would me more helpful if you share Sample Qvw.

[vikasmahajan]

give couple of minutes i will send qvw.

Vikas

[#5]

@stalwar1gwassenaar

Sir, what would be your approach here.

Please help.

[#6]

Thank You Vikas.

[Peter_Cammaert]

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

[#8]

Thanks Peter Sir for your reply.

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

[sasiparupudi1]

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

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