development days in load script

[sai_12]

Hi all i have a simialr table as below

client	catgrory	date	development days
11	a	1/02/2003	0
11	a	2/02/2003	1
11	b	3/02/2003	2
11	b	4/02/2003	3
11	c	5/02/2003	4
22	b	12/03/2005	0
22	c	14/03/2005	2
22	a	15/03/2005	3
22	b	16/03/2005	4

A client can buy from a category on certain date, i want to create  a new field where i want to make min(date) per client as 0 and the others should be date- (min(date))

if( date=min(date) per client then 0, else give date-min(date))

on load script

Any help is much appreciated, cheers

[vincent_ardiet_]

You can create a mapping table with [client] and Min([date]).
Then, you enrich the main table applying the mapping to compute the difference.

[Aasir]

You can achieve this by using the Group By clause along with the ApplyMap function.

// Load your original data
YourTable:
LOAD
client,
catgrory,
date,
developmentDays
FROM [Your Data Source];

// Create a mapping table to get the minimum date for each client
MinMaxDates:
LOAD
client,
Min(date) as MinDate
RESIDENT YourTable
GROUP BY client;

// Apply the mapping to get the minimum date for each row
YourTable:
LOAD
client,
catgrory,
date,
developmentDays,
ApplyMap('MinMaxDates', client) as MinDate
RESIDENT YourTable;

// Create the new field as per your condition
YourTable:
LOAD
client,
catgrory,
date,
developmentDays,
If(date = MinDate, 0, date - MinDate) as NewField
RESIDENT YourTable;

[sai_12]

can you tell me how to do this

i am trying

[map1]:
mapping Load
client,
min([date]) as [Min_Service_From_per_Client]
resident [table]
group By client;

newtable:

load *,

applymap(map1,date,date-Min_Service_From_per_Client) as development_months

resident table;

drop table

i am getting error map_id not found and also how will i get 0 if min (date)= service_Date

[vincent_ardiet_]

Like this should be better:

load *,

date-applymap('map1',client) as development_months

resident table;

[Ahidhar]

try this

tab:
load * Inline
[
client, category, date
11, a, 1/02/2003
11, a, 2/02/2003
11, b, 3/02/2003
11, b, 4/02/2003
11, c, 5/02/2003
22, b, 12/03/2005
22, c, 14/03/2005
22, a, 15/03/2005
22, b, 16/03/2005
];

left join(tab)
load
client,
min(date) as mindate
resident tab
group by client;

tab1:
load
client,category,date,
if(date=mindate,0,date-mindate) as development_days
resident tab;
drop table tab;