Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Hi everyone, problem with dates

Hi,

I've a table like that:

Immagine.jpg

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

1 Solution

Accepted Solutions
Contributor III
Contributor III

[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

View solution in original post

14 Replies
Contributor III
Contributor III

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:

test.PNG

Hope this helps.

Kind regards,

Jop

Not applicable

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

Contributor III
Contributor III

Hi Miriana,

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

Kind regards,

Jop

Not applicable

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

Contributor III
Contributor III

[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

View solution in original post

Not applicable

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

Contributor III
Contributor III

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.

Not applicable

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

Contributor III
Contributor III

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