topic Re: Load only first record per ID in QlikView

Load only first record per ID

pauldamen — Mon, 13 Feb 2017 13:32:48 GMT

All,

I have a dataset which looks like this:

ID Status Date

1 Declined 1/1

1 Declined 3/1

1 Approved 5/1

2 Approved 1/1

3 Declined 2/1

3 Approved 3/1

What I need is only to load the first record of every ID, so I get this load:

ID Status Date

1 Declined 1/1

2 Approved 1/1

3 Declined 2/1

How can I achieve this?

Regards,

Paul

Re: Load only first record per ID

pipuindia99 — Mon, 13 Feb 2017 13:35:52 GMT

Hi,

Try this

load

firstsortedvalue(ID) as ID,

status,

Date

from table name group by status, date;

Re: Load only first record per ID

Anonymous — Mon, 13 Feb 2017 13:37:51 GMT

LOAD

firstsortedvalue(Date,Date) as Date,

firstsortedvalue(Status,Date) as Status,

resident...

group by ID;

-------------------------

But you have to be carefull, when the first sorted value isn't unique within each dimension.

Re: Load only first record per ID

pipuindia99 — Mon, 13 Feb 2017 13:42:47 GMT

Hi,

sorry, Try the below one

load

firstsortedvalue(ID , ID) as ID,

status,

Date

from table name group by status, date;

Re: Load only first record per ID

pauldamen — Mon, 13 Feb 2017 13:48:27 GMT

I still get multiple lines for each ID. Please find my workbook attached

Re: Load only first record per ID

pipuindia99 — Mon, 13 Feb 2017 13:52:27 GMT

Have you attached your workbook? because i could not find it

Re: Load only first record per ID

Anonymous — Mon, 13 Feb 2017 13:56:10 GMT

LOAD * INLINE [
ID, Status, Date
1, Declined , 1/1
1, Declined , 3/1
1, Approved, 5/1
2, Approved, 1/1
3, Declined , 2/1
3, Approved, 3/1
]
Where not Exists(ID);

(works only, if your table is sorted the correct way, but you said, you want the first record)

Re: Load only first record per ID

sunny_talwar — Mon, 13 Feb 2017 13:57:10 GMT

You can try this:

Table:

LOAD * INLINE [

ID, Status, Date

1, Declined, 1/1

1, Declined, 3/1

1, Approved, 5/1

2, Approved, 1/1

3, Declined, 2/1

3, Approved, 3/1

];

Right Join (Table)

LOAD ID,

Min(Date) as Date

Resident Table

Group By ID;

Re: Load only first record per ID

pauldamen — Mon, 13 Feb 2017 13:58:30 GMT

Sorry closed the browser to fast

Re: Load only first record per ID

sunny_talwar — Mon, 13 Feb 2017 14:07:03 GMT

Try this:

FirstValue:

LOAD Winkelwagen as ID,

FirstSortedValue(Status, -[Aanmaak/herzien datum]) as Status,

Date(Min([Aanmaak/herzien datum])) as Date

FROM

[Workflow rapportage SRM v0.2.xlsx]

(ooxml, embedded labels, table is [Mogelijkheid 1])

Group By Winkelwagen;

Re: Load only first record per ID

pauldamen — Mon, 13 Feb 2017 14:16:38 GMT

Not quite. Now I only get approved ID's. I think it needs to sort different, now it takes the first status

Regards, Paul

Re: Load only first record per ID

Anonymous — Mon, 13 Feb 2017 14:20:04 GMT

Do you need the first value of every ID or the firstsortedvalue of every ID?

If you need the firstsortedvalue you have the specify the desired sort weight...

Re: Load only first record per ID

pauldamen — Mon, 13 Feb 2017 14:24:33 GMT

I need the first ID per date.

Re: Load only first record per ID

Anonymous — Mon, 13 Feb 2017 14:27:45 GMT

so if I take Sunny's solution as base, you could try that:

FirstValue:

LOAD Winkelwagen as ID,

FirstSortedValue(distinct Status, [Aanmaak/herzien datum]) as Status,

Date(Min([Aanmaak/herzien datum])) as Date

FROM

[Workflow rapportage SRM v0.2.xlsx]

(ooxml, embedded labels, table is [Mogelijkheid 1])

Group By Winkelwagen;

Re: Load only first record per ID

sunny_talwar — Mon, 13 Feb 2017 14:32:56 GMT

My bad, I added a negative sign... try this:

FirstValue:

LOAD Winkelwagen as ID,

FirstSortedValue(Status, [Aanmaak/herzien datum]) as Status,

Date(Min([Aanmaak/herzien datum])) as Date

FROM

[Workflow rapportage SRM v0.2.xlsx]

(ooxml, embedded labels, table is [Mogelijkheid 1])

Group By Winkelwagen;

Re: Load only first record per ID

yoganantha321 — Mon, 13 Feb 2017 14:51:47 GMT

Hi,

As sunny mentioned it is working properly

Re: Load only first record per ID

Anonymous — Mon, 13 Feb 2017 14:52:27 GMT

ya Its working correctly thanks its helpful to me

Re: Load only first record per ID

MarcoWedel — Mon, 13 Feb 2017 23:20:03 GMT

Hi,

another solution might be:

tabTemp:
LOAD * INLINE [
    ID, Status, Date
    1, Declined, 1/1
    1, Declined, 3/1
    1, Approved, 5/1
    2, Approved, 1/1
    3, Declined, 2/1
    3, Approved, 3/1
];

NoConcatenate

table1:
LOAD * Resident tabTemp
Where ID<>Previous(ID)
Order By ID, Date;

DROP Table tabTemp;

hope this helps

regards

Marco