topic Re: How can I import variable into table in QlikView

How can I import variable into table

suzel404 — Fri, 04 Apr 2014 23:10:55 GMT

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.

Re: How can I import variable into table

Fri, 04 Apr 2014 23:14:19 GMT

Did you try to debug step by step?

Re: Re: How can I import variable into table

suzel404 — Sat, 05 Apr 2014 00:12:37 GMT

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.

Re: How can I import variable into table

mambi — Sat, 05 Apr 2014 00:14:07 GMT

Hi, why not just use :

Re: How can I import variable into table

suzel404 — Sat, 05 Apr 2014 00:30:02 GMT

Thanks Mambi Badi. A simple solution...

Many thanks.

Re: How can I import variable into table

mambi — Sat, 05 Apr 2014 13:44:36 GMT

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