Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

santoshrai
New Contributor

Need help to get count in Script

hi,

Please help me sort this,

i want to get the column in script which will show nos of amendment per material, please guide me to do it.

Data base

Amendment no          po number     material

1121                         55221100     abc123

1122                         55221100     abc123

1123                         55221100     abc123

1234                         55331100     xyz123

2132                         45421121     pqr234

2133                         45421121     pqr234

2134                         45421121     pqr234

2135                         45421121     pqr234

2136                         45421121     pqr234

What i want column in script to get count of nos of amendment per material

Amendment no          po number     material     Count

1121                         55221100     abc123           1

1122                         55221100     abc123           1

1123                         55221100     abc123           2


1234                         55331100     xyz123           1


2132                         45421121     pqr234            1

2133                         45421121     pqr234             2

2134                         45421121     pqr234             3

2135                         45421121     pqr234             4

2136                         45421121     pqr234             5



Thanks in advance.

Tags (1)
6 Replies
sureshqv
Esteemed Contributor III

Re: Need help to get count in Script

hi,

load

*,

coun(distinct Amendment no )  as new count;


.....


from ur data source;

Re: Need help to get count in Script

Not sure how you are getting these numbers?

Capture.PNG

Should it be 1, 2, 3 instead of 1, 1, 2?

Re: Need help to get count in Script

If what I pointed out makes sense, then you can try this script:

Table:

LOAD * Inline [

Amendment no,          po number,    material

1121,                        55221100,    abc123

1122,                        55221100,    abc123

1123,                        55221100,    abc123

1234,                        55331100,    xyz123

2132,                        45421121,    pqr234

2133,                        45421121,    pqr234

2134,                        45421121,    pqr234

2135,                        45421121,    pqr234

2136,                        45421121,    pqr234

];

FinalTable:

LOAD *,

  If(Peek('material') = material, RangeSum(Peek('Count'), 1), 1) as Count

Resident Table

Order By material, [Amendment no];

DROP Table Table;

Capture.PNG

Re: Need help to get count in Script

Data:

Load  Amendment_no,

            po_no,

            material

From Table;

New:

noconcatenate

load *,

        if(rowno()=1 or material<>previous(material),1,peek('Count')+1) as Count

resident Data

order by material;

drop table Data;

santoshrai
New Contributor

Re: Need help to get count in Script

Thanks buddy, rightly spotted out by you...

you solution work fine....Many many thanks.

santoshrai
New Contributor

Re: Need help to get count in Script

Thanks buddy...

Your solution is too good and worked fine for me...

Many Many thanks...

Community Browser