topic Re: Need help: loading in certain sequence in QlikView

Need help: loading in certain sequence

cdss-developer — Fri, 20 Jul 2018 09:12:03 GMT

Hi, I hope someone can help me here.

I have the following two fields (Date and Name):

Date	Name
10-10-2018	Finn
11-10-2018	Liam
12-10-2018	Jesse

Which I would like to load in this way in QlikView:

From	To
10-10-2018 Finn	11-10-2018 Liam
11-10-2018 Liam	12-10-2018 Jesse
12-10-2018 Jesse

Kind regards,

Eelco

Re: Need help: loading in certain sequence

sunny_talwar — Fri, 20 Jul 2018 15:23:20 GMT

Try this

Table:

LOAD * INLINE [

Date, Name

10-10-2018, Finn

11-10-2018, Liam

12-10-2018, Jesse

];

FinalTable:

LOAD *,

Date& ' ' & Name as From,

Previous(Date& ' ' & Name) as To

Resident Table

Order By Date Desc;

DROP Table Table;

Re: Need help: loading in certain sequence

cdss-developer — Mon, 23 Jul 2018 07:09:25 GMT

Yes, that's it , thanks for your quick response!

One more question. When I look at it now, I see that the last line is not necessary.

Is it possible to not print the last line when the 'To' field is empty (So as shown below)?

From	To
10-10-2018 Finn	11-10-2018 Liam
11-10-2018 Liam	12-10-2018 Jesse

Kind regards,

Eelco

Re: Need help: loading in certain sequence

cdss-developer — Mon, 23 Jul 2018 12:21:56 GMT

Hi, I' having another problem. I also added a ID and some extra dates, after which, when I change ID, I still want to see the same order (as described above) but then per ID.

Table:

LOAD * INLINE [

ID, Date, Name,

123, 10-10-2018, Finn

321, 11-10-2018, Liam

321, 12-10-2018, Jesse

123, 12-10-2018, Jesse

123, 14-10-2018, Jesse

123, 13-10-2018, Joep

123, 12-10-2018, Jesse

];

FinalTable:

LOAD *,

Date& ' ' & Name as From,

Previous(Date& ' ' & Name) as To

Resident Table

Order By Date Desc;

DROP Table Table;

When I select both IDs it goes well (stays in the correct order), but when I select them separately it goes wrong:

The second row should start again with 11-10-2018.

Does anyone know how to solve this?

Kind regards,

Eelco

Re: Need help: loading in certain sequence

sunny_talwar — Mon, 23 Jul 2018 15:17:45 GMT

Try this in that case... changes are in red (except the change to the main inline table)...

Table:

LOAD * INLINE [

ID, Date, Name

123, 10-10-2018, Finn

321, 11-10-2018, Liam

321, 12-10-2018, Jesse

123, 12-10-2018, Jesse

123, 14-10-2018, Jesse

123, 13-10-2018, Joep

123, 12-10-2018, Jesse

];

FinalTable:

LOAD *,

Date& ' ' & Name as From,

If(ID = Previous(ID), Previous(Date& ' ' & Name)) as To

Resident Table

Order By ID, Date Desc;

DROP Table Table;

Re: Need help: loading in certain sequence

cdss-developer — Tue, 24 Jul 2018 07:15:03 GMT

Perfect. That's it, thanks!

Re: Need help: loading in certain sequence

cdss-developer — Tue, 24 Jul 2018 07:16:24 GMT

Solved, I added this:

where len(previous (Date) <1) and ID = Previous(ID)