Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
alvinford
Contributor III
Contributor III
‎2012-12-17 10:31 PM

Derive New Field

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.

1,166 Views
0 Likes
1 Solution

Accepted Solutions
Not applicable
‎2012-12-18 04:12 AM

In response to alvinford

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.

View solution in original post

954 Views
0 Likes
15 Replies
sujeetsingh
Master III
Master III
‎2012-12-17 11:26 PM

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

718 Views
0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2012-12-18 01:00 AM

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

-Rob

http://robwunderlich.com

718 Views
sujeetsingh
Master III
Master III
‎2012-12-18 01:28 AM

In response to rwunderlich

Rob ,

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

718 Views
0 Likes
Anonymous
Not applicable
‎2012-12-18 01:37 AM

In response to sujeetsingh

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!

718 Views
0 Likes
alvinford
Contributor III
Contributor III
‎2012-12-18 01:42 AM
Author

In response to rwunderlich

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.

718 Views
0 Likes
alvinford
Contributor III
Contributor III
‎2012-12-18 01:43 AM
Author

In response to rwunderlich

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.

718 Views
0 Likes
alvinford
Contributor III
Contributor III
‎2012-12-18 01:43 AM
Author

In response to rwunderlich

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.

718 Views
0 Likes
alvinford
Contributor III
Contributor III
‎2012-12-18 02:56 AM
Author

In response to sujeetsingh

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

Regards,

Alvin.

718 Views
0 Likes
rlp
Creator
Creator
‎2012-12-18 03:54 AM

In response to alvinford

LEFT JOIN( <your_table> )

LOAD DISTINCT

     ENDDATE ,

     ID ,

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

RESIDENT <your_table> ;

718 Views