topic Re: Derive New Field in QlikView

Derive New Field

alvinford — Tue, 18 Dec 2012 03:31:17 GMT

Hi Struck with the basic thing . I have the following table

StarDate	EndDate	ID
01/01/2010	31/12/2012	540
01/05/2012	31/12/2012	540
01/05/2012	31/12/2012	540
01/05/2012	31/12/2012	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540

I want to create new column based on my EndDate. Want to group the values when EndDate is Same as shown below.

StarDate	EndDate	ID	Group
01/01/2010	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2

Need your help to achieve this.

Regards,

Alvin.

Re: Derive New Field

sujeetsingh — Tue, 18 Dec 2012 04:26:57 GMT

Just try to derive a new column with crosstable function..

go to the following post and you will find the way to deal with.....

http://community.qlik.com/message/185230#185230

Re: Derive New Field

rwunderlich — Tue, 18 Dec 2012 06:00:43 GMT

'Group' & text(autonumber(EndDate)) as Group

-Rob

http://robwunderlich.com

Re: Derive New Field

sujeetsingh — Tue, 18 Dec 2012 06:28:05 GMT

Rob ,

A good and short way can you please explain the function used,,

Re: Derive New Field

Anonymous — Tue, 18 Dec 2012 06:37:32 GMT

Rob's simple solution is actually quite genious.

Each unique value in EndDate will get a new number assigned by the Autonumber() function.

This number is then concatenated with the word 'Group' to create Group1, Group2 etc.

Simple and elegant!

Re: Derive New Field

alvinford — Tue, 18 Dec 2012 06:42:39 GMT

Hi Rob,

Thanks for your reply. I need to group the values based upon ID and EndDate as shown below.

StartDate	EndDate	Group	ID
01/01/2010	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
24/12/2010	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250

Request help to achieve it.

Regards,

Alvin.

Re: Derive New Field

alvinford — Tue, 18 Dec 2012 06:43:06 GMT

Hi Rob,

Thanks for your reply. I need to group the values based upon ID and EndDate as shown below.

StartDate	EndDate	Group	ID
01/01/2010	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
24/12/2010	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250

Request help to achieve it.

Regards,

Alvin.

Re: Derive New Field

alvinford — Tue, 18 Dec 2012 06:43:51 GMT

Hi Rob,

Thanks for your reply. I need to group the values based upon ID and EndDate as shown below.

StartDate	EndDate	Group	ID
01/01/2010	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/05/2012	31/12/2012	Group1	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
01/01/2013	28/02/2013	Group2	540
24/12/2010	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
01/05/2012	23/12/2012	Group1	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
24/12/2012	28/02/2013	Group2	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250
01/03/2013	31/08/2013	Group3	250

Request help to achieve it.

Regards,

Alvin.

Re: Derive New Field

alvinford — Tue, 18 Dec 2012 07:56:20 GMT

Hi Thanks for sharing the Post. This doesn't solve my problem.

Regards,

Alvin.

Re: Derive New Field

rlp — Tue, 18 Dec 2012 08:54:46 GMT

LEFT JOIN( <your_table> )

LOAD DISTINCT

ENDDATE ,

ID ,

Group & '-' & text( autonumber( ENDDATE & '-' & ID )) as Group

RESIDENT <your_table> ;

Re: Derive New Field

Tue, 18 Dec 2012 09:12:08 GMT

Hi Alvin,

U can write a Script like Below

Tb1:

Load Distinct

ID

,Start_Date,End_Date
,'Group'&Text(AutoNumber(End_Date,ID)) as Group;

Load * Inline

[Start_Date,End_Date,ID

01/01/2010,31/12/2012,540

01/05/2012,31/12/2012,540

01/05/2012,31/12/2012,540

01/05/2012,31/12/2012,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

24/12/2010,23/12/2012,250

01/05/2012,23/12/2012,250

01/05/2012,23/12/2012,250

01/05/2012,23/12/2012,250

24/12/2012,29/02/2013,250

24/12/2012,29/02/2013,250

01/03/2012,31/08/2013,250

01/03/2013,31/08/2013,250

];

Regards,

Kabilan K.

Re: Derive New Field

alvinford — Tue, 18 Dec 2012 09:46:06 GMT

Hi Richard,

May be I have not stated it correctly. I have the following Fields.

ID	StartDate	EndDate
540	01/01/2010	31/12/2012
540	01/05/2012	31/12/2012
540	01/05/2012	31/12/2012
540	01/05/2012	31/12/2012
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
250	24/12/2010	23/12/2012
250	01/05/2012	23/12/2012
250	01/05/2012	23/12/2012
250	01/05/2012	23/12/2012
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013

I have to derive a new field Group. I have to group the EndDate for each ID as shown below

ID	StartDate	EndDate	Group
540	01/01/2010	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
250	24/12/2010	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3

For Example for the ID 540 there are two repeated values in the EndDate the First Value is grouped as Group1 and the Second Value is Grouped as Group2. For the ID 250 there are three repeated values in the EndDate so in the Group Column there are three values in the Group i.e. Group1,Group2 and Group3.

Need your assistance .

Regards,

Alvin.

Re: Derive New Field

Tue, 18 Dec 2012 09:53:07 GMT

Hi,

derive field =

'Group'&Text(AutoNumber(End_Date,ID)) as Group;

Pls see the attachment

Re: Derive New Field

rlp — Tue, 18 Dec 2012 10:25:32 GMT

You can generate an indice with RecNo() but, given that your index must be generated for each ID, you should iterate on the ID. For that, you should first isolate yout diiferents ID and then construct your table for each ID.

This gives the following code:

tmp_ID:

NOConcatenate

LOAD DISTINCT

RESIDENT <your_table> ;

FOR ID_indice=0 TO NOOfRows( 'tmp_ID' )

SET current_ID = peek( 'ID' , $(#ID_indice) , 'tmp_ID' ) ;

table_to_join:

LOAD DISTINCT

ID ,

EndDate ,

'Group' & RowNo() as Group

RESIDENT <your_table>

WHERE ID='current_ID' ;

NEXT ID_indice ;

DROP TABLE tmp_ID ;

LEFT JOIN( <your_table> )

LOAD DISTINCT

RESIDENT table_to_join ;

DROP TABLE table_to_join ;

Re: Derive New Field

rlp — Tue, 18 Dec 2012 11:58:15 GMT

I forgot to order by dates so the code for table_to_join should be:

table_to_join:

LOAD DISTINCT

...

RESIDENT <your_table>

WHERE ID='$(current_ID)'

ORDER BY EndDate ASC

;

Re: Derive New Field

rwunderlich — Tue, 18 Dec 2012 17:47:59 GMT

I think Kabilan has it right. You want a set of counters for each ID, correct?

autonumber(EbdDate,ID)

will use a seperate pool of numbers for each ID.

-Rob