Hi!! Please help me to get the desired data..file is attached

[#1]

Hi!! Please help me to get the desired data..file is attached

[sunny_talwar]

Not sure I understand the logic behind the desired output. Would you be able to offer more details as how you want the desired data table to look like? Is it merged into sales data or sales return data? or is it a stand along table?

[#3]

hi Sunny..Thanks for your response!!

we have different tables for Sales and Sales return data.

I want to Show for a sales return which was the previous sales date..for Same item and batch..

[marjan_it]

both of your example return is for previous date. what do you mean exactly?!!

[#5]

Thanks Marjan for considering,

Suppose some quantities of my item1 sold on dates 1,3,4,6 of Jan..

now suppose some quantities of same item1, i got as sales return on date 5 Jan....

Now i want to know what is my last sale date before the date of return for that item1 i.e. 4 Jan....

My ques is how can i get this 4 Jan..

Hopes this will clear the requirement..

[swuehl]

Maybe like this

[SALES DATA]:
LOAD *, 1 as Type INLINE [
ITEM DATE SALE QTY
A 01.01.2016 10
A 02.01.2016 10
A 03.01.2016 10
B 04.01.2016 10
B 05.01.2016 10
B 06.01.2016 10
] (delimiter is '\t');


//


[SALES RETURN DATA]:
Concatenate
LOAD *,2 as Type INLINE [
ITEM DATE SALE RETURN QTY
A 03.01.2016 2
B 05.01.2016 3
] (delimiter is '\t');




FACT:
LOAD *,
  If(Len([SALE RETURN QTY]), Peek('DATE')) as [LAST SALE DATE]
Resident [SALES DATA]
ORDER BY ITEM, DATE ASC, Type ASC;


DROP TABLE [SALES DATA]; 

ITEM	DATE	SALE QTY	SALE RETURN QTY	LAST SALE DATE	Type
A	01.01.2016	10			1
A	02.01.2016	10			1
A	03.01.2016	10			1
A	03.01.2016		2	03.01.2016	2
B	04.01.2016	10			1
B	05.01.2016	10			1
B	05.01.2016		3	05.01.2016	2
B	06.01.2016	10			1