topic Re: First occurances in QlikView

First occurances

Wed, 05 Feb 2014 16:00:39 GMT

Hi All,

I have table like this:

FAMILY	TYPE	PART	ACTION	FINAL	QUANTITY
100	A 2.5	A1	ACT A	Y	70
100	A 3.0	A2	ACT B	N	100
200	B 2.5	B1	ACT F	Y	10
200	B 6.5	B3	ACT M	Y	0
100	A 9.0	A5	ACT A	Y	20

I want do get in another table only the first occurance of each family. In this example, the result table will be:

FAMILY	TYPE	PART	ACTION	FINAL	QUANTITY
100	A 2.5	A1	ACT A	Y	70
200	B 2.5	B1	ACT F	Y	10

Thanks in advance,

Itsik

Re: First occurances

Nicole-Smith — Wed, 05 Feb 2014 16:12:33 GMT

In your load script, you can add a flag like this:

Data:

LOAD *, exists(FAMILY, FAMILY) as FirstValueFlag INLINE [

    FAMILY, TYPE, PART, ACTION, FINAL, QUANTITY

    100, A 2.5, A1, ACT A, Y, 70

    100, A 3.0, A2, ACT B, N, 100

    200, B 2.5, B1, ACT F, Y, 10

    200, B 6.5, B3, ACT M, Y, 0

    100, A 9.0, A5, ACT A, Y, 20

];

And then you can use set analysis like {<FirstValueFlag={0}>} in your expression in order to only get the first occurences.

I have also attached an example.

Re: First occurances

Wed, 05 Feb 2014 16:43:31 GMT

I'm trying do this trick on not INLINE table without success...

Re: First occurances

Nicole-Smith — Wed, 05 Feb 2014 16:46:01 GMT

Please post the load script for your table.

Re: First occurances

Wed, 05 Feb 2014 16:51:57 GMT

I have this SQL Statement:

Load

FAMILY,

TYPE,

PART,

ACTION,

FINAL,

QUANTITY

Resident Test_tbl;

That create the PP table.

I want to do this manipulated that u posted on this statement...

Re: First occurances

Nicole-Smith — Wed, 05 Feb 2014 17:04:56 GMT

What is the SQL statement? That is a resident load.

Re: First occurances

maxgro — Wed, 05 Feb 2014 17:05:58 GMT

Source:

load rowno() as ID, *;

LOAD * INLINE [

FAMILY, TYPE, PART, ACTION, FINAL, QUANTITY

100, A 2.5, A1, ACT A, Y, 70

100, A 3.0, A2, ACT B, N, 100

200, B 2.5, B1, ACT F, Y, 10

200, B 6.5, B3, ACT M, Y, 0

100, A 9.0, A5, ACT A, Y, 20

];

Table:

NoConcatenate

load * resident Source where peek(FAMILY) <> FAMILY order by FAMILY, ID;

DROP Table Source;