How can I import variable into table

[suzel404]

Hi all,

How can I import variable into table ?

My script :

LOAD * INLINE [
    ID_MVT, DATE_START, STATE, DATE_END,
    1001, 02/01/2014 16:21:50, 101, 30/12/2013 00:00:00
    1001, 02/01/2014 16:21:50, 102, 31/12/2013 07:44:00
    1001, 02/01/2014 16:21:50, 103, 31/12/2013 09:00:00
    1001, 02/01/2014 16:21:50, 104, 02/01/2014 10:28:01
    1001, 02/01/2014 16:21:50, 105, 02/01/2014 17:37:00
];
MinMax:
LOAD
Max(DATE_START) as MaxDate_Start,
Max(DATE_END) as MaxDate_End
RESIDENT Table1;

LET vMaxDate_Start = Date(Peek('MaxDate_Start'),'DD/MM/YYYY hh:mm:ss');
LET vMaxDate_End = Date(Peek('MaxDate_End'),'DD/MM/YYYY hh:mm:ss');

DROP TABLE MinMax;

TABLE:
LOAD
$(vMaxDate_Start) as MaxDate_Start,
$(vMaxDate_End) as MaxDate_End;
LOAD
ID_MVT,
DATE_START,
STATE,
DATE_END
RESIDENT Table1;
DROP TABLE Table1;

I want to have a new table with fields : ID_MVT, DATE_START, STATE, DATE_END, MaxDate_Event, MaxDate_Step.

I have actually an error syntax near FROM.

Can someone helps me ?

Thank in advance.

[#2]

Did you try to debug step by step?

[suzel404]

Hi Dariusz,

I've found a solution for variables but How can I integrated all fields in table Table1 ?

LOAD
Max(DATE_START) as MaxDate_Start,
Max(DATE_END) as MaxDate_End
RESIDENT Table1;


LET vMaxDate_Start = Num(Peek('MaxDate_Start',0,'MinMax'));
LET vMaxDate_End = Num(Peek('MaxDate_End',0,'MinMax'));

DROP TABLE MinMax;



TABLE:
LOAD
Date($(#vMaxDate_Start)) as MaxDateStart,
Date($(#vMaxDate_End)) as MaxDateEnd
AutoGenerate(1);

LOAD
//If(MaxDateStart > MaxDateEnd , 'YES', 'NO') as _FlagKO,

ID_MVT as ID,
DATE_START as START_DATE,
STATE as STATE,
DATE_END as END_DATE
RESIDENT Table1;
DROP TABLE Table1;

I want to integrated the fields MaxDateStart and MaxDatreEnd into one table : Table1.

[mambi · Accepted solution]

Hi, why not just use :

Table1:
LOAD * INLINE [
     ID_MVT, DATE_START, STATE, DATE_END,
     1001, 02/01/2014 16:21:50, 101, 30/12/2013 00:00:00
     1001, 02/01/2014 16:21:50, 102, 31/12/2013 07:44:00
     1001, 02/01/2014 16:21:50, 103, 31/12/2013 09:00:00
     1001, 02/01/2014 16:21:50, 104, 02/01/2014 10:28:01
     1001, 02/01/2014 16:21:50, 105, 02/01/2014 17:37:00
];

left join(Table1)
LOAD ID_MVT,
Max(DATE_START) as MaxDate_Start,
Max(DATE_END) as MaxDate_End
RESIDENT Table1 Group by ID_MVT;

[suzel404]

Thanks Mambi Badi. A simple solution...

Many thanks.

[mambi]

you can also use your first method like this :

Table1:
LOAD * INLINE [
     ID_MVT, DATE_START, STATE, DATE_END,
     1001, 02/01/2014 16:21:50, 101, 30/12/2013 00:00:00
     1001, 02/01/2014 16:21:50, 102, 31/12/2013 07:44:00
     1001, 02/01/2014 16:21:50, 103, 31/12/2013 09:00:00
     1001, 02/01/2014 16:21:50, 104, 02/01/2014 10:28:01
     1001, 02/01/2014 16:21:50, 105, 02/01/2014 17:37:00
];
MinMax:
LOAD
Max(DATE_START) as MaxDate_Start,
Max(DATE_END) as MaxDate_End
RESIDENT Table1;

LET vMaxDate_Start = Date(Peek('MaxDate_Start'),'DD/MM/YYYY hh:mm:ss');
LET vMaxDate_End = Date(Peek('MaxDate_End'),'DD/MM/YYYY hh:mm:ss');

DROP TABLE MinMax;

TABLE:
LOAD
ID_MVT,
DATE_START,
STATE,
DATE_END,
'$(vMaxDate_Start)' as MaxDate_Start,
'$(vMaxDate_End)' as MaxDate_End
RESIDENT Table1;
DROP TABLE Table1; 

but the problem with this is : if you have many ID_MVT for exemple 1001,1002... then MaxDate_Start and MaxDate_Start will be for all ID_MVT instead of each ID_MVT