topic Re: First Record in QlikView

First Record

upaliwije — Wed, 22 Apr 2015 10:42:06 GMT

Dear Friends

I have following data in a my data table

Policy_No Month

1 1

1 2

1 4

1 3

2 2

2 3

2 5

3 4

3 5

3 6

3 7

I want to write a syntax in data load to obtain 1st month of each policy Eg Following format

Policy_No Month 1st Month

1 1 1

1 2

1 4

1 3

2 2 2

2 3

2 5

3 4 4

3 5

3 6

3 7

PLS HELP ME TO WRITE THE SYNTAX

Re: First Record

ali_hijazi — Wed, 22 Apr 2015 10:47:10 GMT

after loading your table write the following:

temp:

load Policy_no, min(Month) as Month

resident your_table

group by Policy_No;

left join(your_table)

load * resident temp;

drop table temp;

Re: First Record

maxgro — Wed, 22 Apr 2015 10:51:59 GMT

load * inline [

Policy_No , Month

1 , 1

1 , 2

1 , 4

1 , 3

2 , 2

2 , 3

2 , 5

3 , 4

3 , 5

3 , 6

3 , 7

];

NoConcatenate load

if(Policy_No <> Peek(Policy_No), Month) as Flag

Resident a order by Policy_No , Month;

DROP Table a;

Re: First Record

sunny_talwar — Wed, 22 Apr 2015 10:58:30 GMT

Is this the desired output?

If yes then look at the script and also find attached a sample app.

Table:

LOAD * INLINE [

Policy_No, Month

1, 1

1, 2

1, 4

1, 3

2, 2

2, 3

2, 5

3, 4

3, 5

3, 6

3, 7

];

Join(Table)

LOAD Policy_No,

FirstValue(Month) as [1st Month]

Resident Table

Group By Policy_No

Order By Policy_No, Month;

Best,

Sunny

Re: First Record

anbu1984 — Wed, 22 Apr 2015 11:05:21 GMT

T1:
Load * Inline [
Policy_No,Month
1,1
1,2
1,4
1,3
2,2
2,3
2,5
3,4
3,5
3,6
3,7 ];

NoConcatenate
Load *,If(AutoNumber(RecNo(),Policy_No)=1,Month) Resident T1 Order By Policy_No,Month;

Drop Table T1;

Re: First Record

upaliwije — Wed, 22 Apr 2015 23:05:07 GMT

Thanks for your reply. Your syntax is correct for my requirement. But my require differ little bit I want 1st month against all records as follows

Policy_No Month 1st Month

1 1 1

1 2 1

1 4 1

1 3 1

Re: First Record

maxgro — Wed, 22 Apr 2015 23:16:37 GMT

this?

load * inline [

Policy_No , Month

1 , 1

1 , 2

1 , 4

1 , 3

2 , 2

2 , 3

2 , 5

3 , 4

3 , 5

3 , 6

3 , 7

];

NoConcatenate load

Policy_No, Month,

if(isnull(Flag), Peek(Flag), Flag) as Flag;

NoConcatenate load

if(Policy_No <> Peek(Policy_No), Month) as Flag

Resident a order by Policy_No , Month;

DROP Table a;

Re: First Record

upaliwije — Thu, 23 Apr 2015 05:59:40 GMT

Thanks all