topic Re: Hi everyone, problem with dates in QlikView

Hi everyone, problem with dates

Thu, 26 Mar 2015 09:00:18 GMT

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

Re: Hi everyone, problem with dates

jopmoekotte — Thu, 26 Mar 2015 15:29:50 GMT

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

Re: Hi everyone, problem with dates

Fri, 27 Mar 2015 11:09:58 GMT

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

Re: Hi everyone, problem with dates

jopmoekotte — Fri, 27 Mar 2015 11:43:31 GMT

Hi Miriana,

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

Kind regards,

Jop

Re: Hi everyone, problem with dates

Fri, 27 Mar 2015 12:09:31 GMT

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

Re: Hi everyone, problem with dates

jopmoekotte — Fri, 27 Mar 2015 12:18:23 GMT

[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

Re: Hi everyone, problem with dates

Fri, 27 Mar 2015 14:05:06 GMT

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

Re: Hi everyone, problem with dates

jopmoekotte — Fri, 27 Mar 2015 14:11:53 GMT

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.

Re: Hi everyone, problem with dates

Fri, 27 Mar 2015 14:22:40 GMT

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

Re: Hi everyone, problem with dates

jopmoekotte — Fri, 27 Mar 2015 15:15:55 GMT

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

Re: Hi everyone, problem with dates

Fri, 27 Mar 2015 16:39:52 GMT

It works!

But it gives me this result..

Best regards

Re: Hi everyone, problem with dates

jopmoekotte — Fri, 27 Mar 2015 21:21:04 GMT

try replacing




 [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;

with


 [Data]:  
Noconcatenate LOAD *,  
  If(Codice=Peek(Codice) and Sede=Peek(Sede),Date#(Data-Peek(Data),'D'),0) as Day_nr  

RESIDENT Data_temp Order By Codice, Recordnr ASC;

Re: Hi everyone, problem with dates

Mon, 30 Mar 2015 07:08:01 GMT

Sorry but with Noconcatenate it gives me this error:

Si è verificato il seguente errore:

Field not found

L'errore si è verificato qui:

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

Re: Hi everyone, problem with dates

jopmoekotte — Mon, 30 Mar 2015 07:30:32 GMT

Hi Miriana,

It's not the Noconcatenate that gives the issue, you're not loading Codice. If you rename that field you should also order by the new fieldname at the end.

RESIDENT Data_temp Order By Codice, Recordnr ASC

RESIDENT Data_temp Order By Telaio, Recordnr ASC

Kind regards,

Jop

Re: Hi everyone, problem with dates

Mon, 30 Mar 2015 07:38:30 GMT

Sorry, I didn't see it.

I've replace Codice with Telaio now, but it gives me the same result of the table I've posted friday.

No matter, I'll do it in another way.

Thank you very much for the help.

Kind Regards

Miriana