Need to fetch desired field based on conditions

[ashok1203]

Hi All,

We have a table having Some fields. Two are Date fields Date1 & Date2. In respect of Date1 field there is a Value1 field. We need to derive the Value2 with respect to date2. Value2 is depend on 3 fields Customer, Date1 and Value1.

Example in Excel Sheet:: For C1 ,Date1 is 01/01/2016 and Value is 100.Therefore for Date2 we need to derive the value2.

Logic is that if for Same Customer Date1 <=Date2 then Value1 will be the value2.

For Column 1 Value of Value2 will be 100 because Date1=Date2.

Other case Column2 ::Date1 > Date2 ,In this case we will see the upper value of Date1 if this Date <= Date2 then Value of Value1 for that will  be the Value of Value2. For Column2 Value2 will be 100.

Same for column3 Value2 will be 100 because Date1> date2. So we are always assuming the value of value2 ,according to date2 where Date1<=Date2.

If not then we assuming previous value. If the previous Date is also large then we will check upper of the previous value. This is our overall logic. 

Please find the attachment for more Info and Kindly do needful...

Thanks in Advance......!

[tamilarasu]

Hi Ashok,

If I understood correctly, below screenshot (Value (New)) will be the correct result. I have attached a sample file.

TempData:
LOAD Customer,
Date1,
Value1,
Date2,
[Value (New)]
FROM

(ooxml, embedded labels, table is Sales);

ResultData:
NoConcatenate
Load *,If(Peek('Customer')<>Customer,Value1,If(Date1<=Date2,Value1,Peek('Value1'))) as Value2
Resident TempData order by Customer, Date1;

DROP Table TempData;

[ashok1203]

Hi Nagaraj,

The Query last step you missed. For better understaning i will explain it clearly in below:

If the Date1<=Date2 then directly we are fetching value1 as value2. In other case i.e. Date1>Date2 then,

we can check the previous row date1 value and again compare that date value with date2. In that case if the Date1 value is also greater than same procedure like above. i.e. again goto previous row date1 value and compare it date2 like this.

Example:

Date1: 1/30/2016 Date2: 1/5/2016 in this case the Date1 should be greater date compare to Date2. So we can take the previous date1 value i.e. 1/20/2016. so, again i compared 1/20/2016 with 1/5/2016 this case also date1 is greater than Date2. So, again previous row Date1 value. i.e. 1/1/2016 . Now we can compare 1/1/2016 date with 1/5/2016. For this case the value1 as value2. So, Value2 here is 100. That is the logic i am expecting.

Thanks in Advance......!

[tamilarasu · Accepted solution]

Hello Ashok,

Have a look at the attachment. Test with all scenarios and let me know.

[ashok1203]

Thanks Nagaraj,

It's Working fine.....

Regards,

Ashok