topic Re: Load first 10 records in a group in QlikView

Load first 2 records in a group

Wed, 19 Mar 2014 09:06:37 GMT

Hi,

anyone have any idea how to load only the first 2 records according to the latest birthday of every group?

Expected Result:

For Group 1, Alice,Jon

For Group 2, David,Derrick

For Group 3, Serene,Rachel

Dataset:

Name	Birthday	Group
Alice	01/01/87	1
David	02/03/87	2
Jason	29/04/87	3
Jon	01/04/87	1
Derrick	03/05/87	2
Aaron	17/07/87	2
Jerome	20/09/87	1
Serene	23/02/87	3
Rachel	21/04/87	3

Thank you.

Re: Load first 10 records in a group

kumarnatarajan — Wed, 19 Mar 2014 09:16:32 GMT

Hi,

Load below script.

LOAD * Where WildMatch(AutoNumber(Group&RowNo(),Group),1,2);

LOAD * INLINE [

Name, Birthday, Group

Alice, 01/01/87, 1

David, 02/03/87, 2

Jason, 29/04/87, 3

Jon, 01/04/87, 1

Derrick, 03/05/87, 2

Aaron, 17/07/87, 2

Jerome, 20/09/87, 1

Serene, 23/02/87, 3

Rachel, 21/04/87, 3

];

I think For Group 3, Jason,Serene

Re: Load first 10 records in a group

Wed, 19 Mar 2014 09:28:47 GMT

Group 3 should be Serene and Rachel because I will order by the birthday and get the first 2 records of it.

Re: Load first 2 records in a group

kumarnatarajan — Wed, 19 Mar 2014 09:32:13 GMT

Ok No prob. you can get result after using order by.

try below one.

Temp:

LOAD * INLINE [

Name, Birthday, Group

Alice, 01/01/87, 1

David, 02/03/87, 2

Jason, 29/04/87, 3

Jon, 01/04/87, 1

Derrick, 03/05/87, 2

Aaron, 17/07/87, 2

Jerome, 20/09/87, 1

Serene, 23/02/87, 3

Rachel, 21/04/87, 3

];

NoConcatenate

Temp2:

LOAD * Resident Temp Where WildMatch(AutoNumber(Group&RowNo(),Group),1,2) Order by Birthday;

DROP Table Temp;

Re: Re: Load first 10 records in a group

SunilChauhan — Wed, 19 Mar 2014 09:45:05 GMT

see the attached file

hope this helps