Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

How do I count the # of records within a group?

Morning everyone!

Please take a look at this table:

   

NumberTotal
7642510000000
76425--
76425--
511185000000
51118--
428892500000
462232500000
3899910000000
38999--
38999

--

It's a bit of a strange table: some numbers have a total, some don't (NULL). Now, what I want is to substract a certain amount from the total in the first record (which has a total) DEPENDING on the number of records within the Group (Group by Number).

If there are 3 records, I need to substract 7,500,000, when there are 2 I need to substract 5,000,000 and when there is 1 record, I don't need to do anything.

How is this accomplished?

Any help is greatly appreciated!!

Tags (1)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: How do I count the # of records within a group?

Temp:
LOAD *,AutoNumber(RowNo(),Number) as A Inline [
Number,Total
76425, 10000000
76425,
76425,
51118, 5000000
51118, 
42889, 2500000
46223, 2500000
38999, 10000000
38999, 
38999,
]
;
Join LOAD Number,Count(Number)-1 as Count
Resident Temp Group By Number;
LOAD Number,Total,RowNo() as ID,
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table
Temp;

9 Replies
antoniotiman
Honored Contributor III

Re: How do I count the # of records within a group?

Hi Hans,

maybe like this

Temp:
LOAD *,AutoNumber(RowNo(),Number) as A Inline [
Number,Total
76425, 10000000
76425,
76425,
51118, 5000000
51118, 
42889, 2500000
46223, 2500000
38999, 10000000
38999, 
38999,
]
;
Join LOAD Number,Count(Number) as Count
Resident Temp Group By Number;
LOAD Number,Total,RowNo() as ID,
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table
Temp;

Regards,

Antonio

effinty2112
Honored Contributor

Re: How do I count the # of records within a group?

Hi Hans,

Try this straight table:

Number sum(Total) Rangesum(sum(Total), - if(count(Number) >1, count(Number) *2500000))
38999100000002500000
4288925000002500000
4622325000002500000
5111850000000
76425100000002500000

Let me know if you need it done in the script instead.

cheers

Andrew

hansdevr
Contributor II

Re: How do I count the # of records within a group?

Hi Antonio and good morning to you!

Thnx for your swift reply - Gonna try that straight away!

hansdevr
Contributor II

Re: How do I count the # of records within a group?

Hi Andrew and thnx!!,

Yes I need it done in the load script...

effinty2112
Honored Contributor

Re: How do I count the # of records within a group?

Hi Hans,

Maybe this:

Data:

LOAD

RowNo() as Row,

*;

LOAD * INLINE [

    Number, Total

    76425, 10000000

    76425

    76425

    51118, 5000000

    51118

    42889, 2500000

    46223, 2500000

    38999, 10000000

    38999

    38999,

];

MapDeduction:

Mapping

LOAD

Number,

if(count(Number)>1,count(Number)*2500000,0)

Resident Data Group by Number;

Left Join (Data)

LOAD

Distinct

Number,

Total,

if(IsNum(Total),Total - ApplyMap('MapDeduction',Number)) as NewTotal

Resident Data;

Giving this tablebox:

Row Number Total NewTotal
176425100000002500000
276425  
376425  
45111850000000
551118  
64288925000002500000
74622325000002500000
838999100000002500000
938999  
1038999

cheers

Andrew

hansdevr
Contributor II

Re: How do I count the # of records within a group?

No, that's not it I'm afraid..

If there's only one number - total remains unchanged;

If there are two equal numbers - total needs to be substracted with 2.5 mln;

if there are three equal numbers - total needs to be substracted with 5 mln.

hansdevr
Contributor II

Re: How do I count the # of records within a group?

Almost, Andrew..

the 5 mln @ 51118 needs to be 2.5 mln, as there are 2 records

antoniotiman
Honored Contributor III

Re: How do I count the # of records within a group?

Temp:
LOAD *,AutoNumber(RowNo(),Number) as A Inline [
Number,Total
76425, 10000000
76425,
76425,
51118, 5000000
51118, 
42889, 2500000
46223, 2500000
38999, 10000000
38999, 
38999,
]
;
Join LOAD Number,Count(Number)-1 as Count
Resident Temp Group By Number;
LOAD Number,Total,RowNo() as ID,
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table
Temp;

hansdevr
Contributor II

Re: How do I count the # of records within a group?

Spot on, Antonio, thnx!!

Community Browser