Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to group the data base on the client No.

Currently, we have a table like below:

ID     client No.  Update date

1      11                6/20/2013   

2      33               6/20/2013

3      11               6/21/2013

4      44               6/21/2013

5      33               6/21/2013

6      55               6/22/2013

7      11               6/22/2013

8      33               6/22/2013

I want to count that the total updated records/new insert records

e.g. get the total updated records on 6/22/2013

count({1<[Update date] = {'6/22/2013'}, ID = {">$(=min(ID))"}>} ID)

the problem is how to get the min id for each client No.?

1 Solution

Accepted Solutions
Not applicable
Author

if you want to get the min id for each client No.

then in script write this code--

TEST:

load

ID,

[client No],

[Update date]

from table;

load

[client No],

min(ID) AS MIN_ID

RESIDENT TEST

GROUP BY [client No]

*********************************************************************

IF YOU want to count that the total updated records/new insert records

THEN TRY THIS CODE

IN EXPRESSION SIDE OF CHART--

DIMENSION1=client No

EXPRESSION-- AGGR(COUNT(Update date),client No)

View solution in original post

5 Replies
Not applicable
Author

if you want to get the min id for each client No.

then in script write this code--

TEST:

load

ID,

[client No],

[Update date]

from table;

load

[client No],

min(ID) AS MIN_ID

RESIDENT TEST

GROUP BY [client No]

*********************************************************************

IF YOU want to count that the total updated records/new insert records

THEN TRY THIS CODE

IN EXPRESSION SIDE OF CHART--

DIMENSION1=client No

EXPRESSION-- AGGR(COUNT(Update date),client No)

Gysbert_Wassenaar

Try adding an IsNew flag field:

Table1:

load

    ID

    [client No],

    [Update date],

    if(exists([client No]), 0, 1) as IsNew

from sourcetable;

You can then use the IsNew field in the expression: count({1<[Update date] = {'6/22/2013'}, IsNew = {0}>} ID)


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you for your replay, I use the slution like yours.

Not applicable
Author

Thank you for your replay, I use the slution like yours.

Not applicable
Author

Thank you for your replay, I use the slution like yours.