finding the middle dates

[prabhas277]

Hi experts,

I have 2 fields  like date,sale..

date,  sales

1/1/2015,100

1/3/2015,300

1/5/2015,500

1/7/2015,700

now my requirement is i want get  the 1/2/2015 date in date filed with sales of 100. and i want get 1/4/2015 date with sales of 300.

How i can get it?

Thanks in advance..

[MK_QSL]

Temp:
Load
  Date(Date#(date,'D/M/YY')) as date,
  sales
Inline
[
  date, sales
  1/1/15,100
  1/3/15,300
  1/5/15,500
  1/7/15,700
];


MinMaxDate:
Load min(date) as MinDate, Max(date) as MaxDate Resident Temp;


Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));


Drop Table MinMaxDate;


Join (Temp)
Load
  Date(MonthStart($(vMinDate) + IterNo() - 1)) as date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


NoConcatenate
Final:
Load
  date,
  If(IsNull(sales) or Len(Trim(sales))=0, Peek('sales'), sales) as sales
Resident Temp
Order By date;


Drop Table Temp;

[MK_QSL · Accepted solution]

Another Approach !

Temp:
Load
  Date(Date#(date,'M/D/YY')) as date,
  sales
Inline
[
  date, sales
  1/1/15,100
  1/3/15,300
  1/5/15,500
  1/7/15,700
];


Left Join (Temp)
Load date, Previous(date) as NextDate 
Resident Temp 
Order By date desc;


NoConcatenate
Final:
Load
  date + IterNo() - 1 as date,
  NextDate,
  sales
Resident Temp
While date + IterNo() - 1 < NextDate;


Drop Table Temp;

[MK_QSL]

If you date is in format M/D/YY, use below,,, if D/M/YY use first reply

Temp:
Load
  Date(Date#(date,'M/D/YY')) as date,
  sales
Inline
[
  date, sales
  1/1/15,100
  1/3/15,200
  1/5/15,300
  1/7/15,400
];


MinMaxDate:
Load min(date) as MinDate, Max(date) as MaxDate Resident Temp;


Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));


Drop Table MinMaxDate;


Join (Temp)
Load
  Date($(vMinDate) + IterNo() - 1) as date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


NoConcatenate
Final:
Load
  date,
  If(IsNull(sales) or Len(Trim(sales))=0, Peek('sales'), sales) as sales
Resident Temp
Order By date;


Drop Table Temp;

[jonathandienst]

This may help: How to populate a sparsely populated field