Hi everyone, problem with dates

[#1]

Hi,

I've a table like that:

I've to count the days that my article "0421j141995" stay in each "Sede".

DEL means Entry and DUL means Exit.

For example this article star from "Sede" 1 for going in "Sede" 6 for staying here for 7 days, then it left "Sede" 6 for going in "Sede" 8 and it stay here for 18 days, etc..

How can I do it in Qlikview or sense?

Thank for everyone can answer.

Best Regards

Miriana

[jopmoekotte]

Hi Miriana,

I have created a small inline test table. Of course the data isn't the same, but this should be the solution to your problem.

You can solve your problem by doing the following:

[Data_temp]:

LOAD *,

  RecNo() as Recordnr

;

LOAD * INLINE [

    Sede, cod.tipo, data, codice

    1, DUL, 16/10/2014, xxxlllsss

    6, DEL, 16/10/2014, xxxlllsss

    6, DUL, 23/10/2014, xxxlllsss

    8, DEL, 23/10/2014, xxxlllsss

    8, DUL, 10/11/2014, xxxlllsss

    1, DEL, 10/11/2014, xxxlllsss

    1, DUL, 11/11/2014, xxxlllsss

    5, DEL, 11/11/2014, xxxlllsss

    5, DUL, 24/12/2014, xxxlllsss

];

LOAD *,

  If(codice=Peek(codice) and Sede=Peek(Sede),Date#(data-Peek(data),'D')) as Day_nr

;

LOAD * RESIDENT Data_temp Order By codice, Recordnr ASC;

the output looks like this:

Hope this helps.

Kind regards,

Jop

[#3]

Hi,

Maybe it can help me if I've only this article, but how can I do it with 8000 articles?

Is it possible?

Best Regards

Miriana

[jopmoekotte]

Hi Miriana,

The script should pick it up for every article without problems, not just 1.

Kind regards,

Jop

[#5]

Hi,

I have to write this in the script?

[Data_temp]:

LOAD *,

  RecNo() as Recordnr

;

LOAD

Sede,

Cod.tipo,

Data,

Codice

FROM [lib://cartel/file.xlsx]

(ooxml, embedded labels, table is Foglio1)

WHERE (example=)Sog='abcd';

LOAD *,

  If(codice=Peek(codice) and Sede=Peek(Sede),Date#(data-Peek(data),'D')) as Day_nr

;

LOAD * RESIDENT Data_temp Order By codice, Recordnr ASC;

It is right?

Kind Regards

Miriana

[jopmoekotte · Accepted solution]

[Data_temp]:

LOAD *,

  RecNo() as Recordnr

;

LOAD

Sede,

Cod.tipo,

Data,

Codice

FROM [lib://cartel/file.xlsx]

(ooxml, embedded labels, table is Foglio1)

WHERE (example=)Sog='abcd';

LOAD *,

  If(Codice=Peek(Codice) and Sede=Peek(Sede),Date#(Data-Peek(Data),'D')) as Day_nr

;

LOAD * RESIDENT Data_temp Order By Codice, Recordnr ASC;

Hi Miriana,

The parts I have marked in bold are the additions to your own data. You shouldn't have to load the part that I created as a dummy file. So the original load statement you have in your file is the basis, the bold parts should be added.

If the regular marked part is your original load statement, this should work!

(Be sure to check spelling and Capital letters)

Kind regards,

Jop

[#7]

Sorry,

it gives me this error:

Si è verificato il seguente errore:

Table not found

L'errore si è verificato qui:

LOAD * RESIDENT Data_temp Order By Codice, Recordnr ASC

[jopmoekotte]

be sure to name your table, you can use another name, but in my example I named the table (at the top) Data_temp.

You can Name a table by writing it above the load statement of that table, in my example:

[Data_temp]:

LOAD *,

  RecNo() as Recordnr

;

etcetera.

[#9]

I've done it, but it load my table twice, so I've my results doubled..

[jopmoekotte]

After the last row in your script add this:

drop table Data_temp;

You might also want to add a Noconcatenate, example:

[Data_temp]:
LOAD *,
  RecNo() as Recordnr
;
LOAD
Sede,
Cod.tipo,
Data,
Codice
FROM [lib://cartel/file.xlsx]
(ooxml, embedded labels, table is Foglio1)
WHERE (example=)Sog='abcd';

 [Data]:
Noconcatenate LOAD *,
  If(Codice=Peek(Codice) and Sede=Peek(Sede),Date#(Data-Peek(Data),'D')) as Day_nr
;
LOAD * RESIDENT Data_temp Order By Codice, Recordnr ASC;
drop table Data_temp;

Kind regards,

Jop