Problem with date

[#1]

Hello!

I need the traceability of the data when there is no date(MONTH-YEAR).I want to fill in every data from the first to the last one. Since there are some data unavailable, I want to fill the blanks with the previous data.

An example:

table A:

ID	MONTH	YEAR	FIELD1	FIELD2	MONTH2	YEAR2	FIELD1
2	2	2005	10	B	3	2005	40
2	5	2005	40	D			40
23	11	2008	25	C			30
23	2	2009	30	H			30
100					6	2010	555
500	3	2015	80	X	1	2015	58
500	6	2015	60	W			58
333	4	2014	100	AA	8	2013	100
333	6	2014	300	BB			100

ID 2, the last date is 5-2005 and the first date is 2-2005. I have no data in 3-2005 and 4-2005 but in these months there is no changes, the data should be the same than in 2-2005.

ID 333 the last date is 8-2013(MONTH2-YEAR2).

ID 500 the first date is 1-2015 and the last date is 6-2015.

For the first date we choose the earliest date between: (MONTH-YEAR / MONTH2-YEAR2) and for the last date we choose the oldest date between: (MONTH-YEAR / MONTH2-YEAR2)

I need this result:

ID	MONTH-MAIN	YEAR MAIN	FIELD1	FIELD2
2	2	2005	10	B
2	3	2005	10	B
2	4	2005	10	B
2	5	2005	40	D
23	11	2008	25	C
23	12	2008	25	C
23	1	2009	25	C
23	2	2009	30	H
100	6	2010	555
500	1	2015	58
500	2	2015	58
500	3	2015	80	X
500	4	2015	80	X
500	5	2015	80	X
500	6	2015	60	W
333	4	2014	100	AA
333	5	2014	100	AA
333	6	2014	300	BB
333	7	2014	300	BB
333	8	2014	300	BB

I just need the start and end dates for each of the products. If product A starting on day 1 and ended on the 10th , the date must be between 1 and 10(MONTH OR MONTH2). If product B started on Day 2 and finished the day only 15 dates there must be enclosed 2 and 15 .
Now it appears from the highest to the lowest date either product A or product B.

I attached with example property of Sunny T with some modifications. It`s very close that I need.

[sunny_talwar · Accepted solution]

This may be:

Table:

LOAD *,

  If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,

  MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2

Inline [

ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B

2,2,2005,10,B,3,2005,40

2,5,2005,40,D,,,40

23,11,2008,25,C,,,30

23,2,2009,30,H,,,30

100,,,,,6,2010,555

500,3,2015,80,X,1,2015,58

500,6,2015,60,W,,,58

333,4,2014,100,AA,8,2014,100

333,6,2014,300,BB,,,100

];

Join (Table)

LOAD ID,

  Value,

  MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR

While AddMonths(Min, IterNo()) <= Max;

LOAD ID,

  If(Max(MONTHYEAR) < Max(MONTHYEAR2), Alt(Max(MONTHYEAR2), Max(MONTHYEAR)), Max(MONTHYEAR)) as Max,

  If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,

  If(Max(MONTHYEAR) < Max(MONTHYEAR2) or Min(MONTHYEAR) > Min(MONTHYEAR2), Only(FIELD1B)) as Value

Resident Table

Group By ID;

FinalTable:

NoConcatenate

LOAD ID,

  MONTH,

  YEAR,

  MONTHYEAR,

  Alt(Alt(FIELD1, FIELD1B), Value) as FIELD1,

  FIELD2;

LOAD ID,

  Num(Month(MONTHYEAR)) as MONTH,

  Year(MONTHYEAR) as YEAR,

  MONTHYEAR,

// MONTH2,

// YEAR2,

  FIELD1B,

  Value,

  If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,

  If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2

Resident Table

Order By ID, MONTHYEAR;

DROP Table Table;

I understand that this may be a sample and a lot of things in the actual database will bring in additional complication. With that in mind, we are here if you need any further help.

Best,

Sunny

[#3]

Hi Sunny,

your answer it`s correct to the example! thank you very much.

Today i will post another issue similar to this. You are a master!

[sunny_talwar]

Sure thing, will be waiting for it