Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Thanks Vikas for the suggestion.
It would me more helpful if you share Sample Qvw.
give couple of minutes i will send qvw.
Vikas
Thank You Vikas.
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
Thanks Peter Sir for your reply.
But I need to Group By Min and Max Dates by 3 Parameters.
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