topic Re: Need help to get count in Script in QlikView

Need help to get count in Script

santoshrai — Wed, 23 Mar 2016 08:34:47 GMT

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.

Re: Need help to get count in Script

Chanty4u — Wed, 23 Mar 2016 09:46:57 GMT

hi,

load

coun(distinct Amendment no ) as new count;

.....

from ur data source;

Re: Need help to get count in Script

sunny_talwar — Wed, 23 Mar 2016 09:49:08 GMT

Not sure how you are getting these numbers?

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

Re: Need help to get count in Script

sunny_talwar — Wed, 23 Mar 2016 09:52:22 GMT

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;

Re: Need help to get count in Script

Kushal_Chawda — Wed, 23 Mar 2016 09:54:13 GMT

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;

Re: Need help to get count in Script

santoshrai — Wed, 23 Mar 2016 11:26:32 GMT

Thanks buddy, rightly spotted out by you...

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

Re: Need help to get count in Script

santoshrai — Wed, 23 Mar 2016 11:27:28 GMT

Thanks buddy...

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

Many Many thanks...