topic Re: how to fill table from other table with request (SQL?) in QlikView

how to fill table from other table with request (SQL?)

Sat, 07 Jul 2012 15:57:37 GMT

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

Re: how to fill table from other table with request (SQL?)

nagaiank — Sat, 07 Jul 2012 16:35:32 GMT

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;

Re: how to fill table from other table with request (SQL?)

Sat, 07 Jul 2012 18:33:27 GMT

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

Re: how to fill table from other table with request (SQL?)

Mon, 09 Jul 2012 13:15:45 GMT

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;