Need help: loading in certain sequence

[cdss-developer]

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

[sunny_talwar]

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;

[cdss-developer]

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

[cdss-developer]

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

[sunny_talwar · Accepted solution]

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;

[cdss-developer]

Perfect. That's it, thanks! 

[cdss-developer]

Solved, I added this:

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