Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need some help for a basic thing I don't know yet.
I'm sure it's very simple for many of yours and you ll be able to save me
Thank you
Stéphane
I've got a table with theses fields :
ID;Statut;Begin-date
123;Open;01/01/2012
123;Work in progress;02/02/2012
123;Affected to MA;03/03/2012
77;Open;01/01/2012
77;Work in progress;02/02/2012
77;Affected to MA;03/03/2012
...
I would like to fill the end date with the begin date of the line which has the same ID and the following date. If there are no line, the end date in now (date of loading data), and if the statut if = 'closed', the endèdate = begin date.
For example, the first line will have the end date of the second line (02/02/2012)
like that :
ID;Statut;Begin-date; end_date
123;Open;01/01/2012;02/02/2012
123;Work in progress;02/02/2012;03/03/2012
123;Affected to MA;03/03/2012; now()
77;Open;01/01/2012;02/02/2012
77;Work in progress;02/02/2012;03/03/2012
77;Affected to MA;03/03/2012: now()
I've got a second table which has only one line per ID, and i would like to fill somme fields with the values of the first table.
In the example, we have only two lines : 123 & 77 ID
The field current statut is the statut of the higher date of the first table for this ID
the begin date of the statut line of the first table which has the value open is put in the "Begin-date of open statut" of this second table
the begin date of the statut line of the first table which has the value closed is put in the "Begin-date of closed statut" of this second table
ID;current_Statut;Begin-date of open statut; Begin-date of closed statut
123 ;Affected to MA ; 01/01/2012
77;Affected to MA ; 01/01/2012
Attached is a solution. The script used in this solution is:
Data:
LOAD * Inline [
ID,Statut,Begin_date
123,Open,01/01/2012
123,Work in progress,02/02/2012
123,Affected to MA,03/03/2012
77,Open,01/01/2012
77,Work in progress,02/02/2012
77,Affected to MA,03/03/2012
];
Table1:
NoConcatenate
LOAD *,If(ID=Previous(ID),Previous(Begin_date),Date(Floor(Today()))) as End_date;
LOAD * Resident Data Order by ID, Begin_date desc;
Drop Table Data;
Data2:
LOAD ID
, FirstSortedValue(Statut,-Begin_date) as Current_Statut
, FirstSortedValue(Begin_date,Begin_date) as Begin_Date
Resident Table1 Group By ID;
Thank you Krishnamoorthy,
Your method seems to be very nice, butI have some difficulties to unterstand it and to put it in my work.
Can you take few minutes to make two examples in my document, in order to learn it and after i will make my own job for all others fields.
http://www.hist-europe.fr/liens/test2.qvw
the two examples are theses one :
- the field [Etat en cours] from Neworigine table has to be filled by the statut value of the table origine for the higher date_changement of the date origine for the same id
- the field date fin of table origine is fill by the field date_changement of the table origine for the following date_changement for the same id
Stéphane
I tested by SQL and that's run.
But datas comes from excel sheet, so i can't use SQL command.
How i can change this SQL request to Qlikview request?
the table is TEST, ans the fileds are id, statut, begin_date and datfin
select id, statut, begin_date, case when test.statut = 'Closed' then test.begin_date else nvl((select min(table1.begin_date) from TEST table1 where table1.begin_date > TEST.begin_date and table1.id=test.id),SYSDATE) end as datfin from TEST;