topic Re: Rowno() count in QlikView

Rowno() count

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

PFA,

Here i want exclude the blanks count .

Partner_Name	Inward_no &' -- '&RowNo()
A	16MS2ADHC7838 -- 1
B	16MS3SOS34969 -- 2
C	16MS3SOS36890 -- 3
D	-- 4
E	-- 5
F	16MS3SOS48772 -- 6
G	16MS3TRB37584 -- 7
H	16MS3TRB40266 -- 8
I	16MS4ADHC6867 -- 9
J	16MS4ADHC6902 -- 10
K	16MS4ADHC6951 -- 11
L	16MS4ADHC7835 -- 12
M	16MS4ADHC7842 -- 13
N	16MS4DEM48171 -- 14
O	16MS4PPNC7830 -- 15
P	16MS4SOS40478 -- 16
Q	16MS4SOS40871 -- 17
R	16MS4SOS43214 -- 18
S	-- 19
T	-- 20
U	-- 21
V	-- 22
W	16MS4SOS46341 -- 23
X	16MS4SOS46500 -- 24
Y	16MS4SOS46501 -- 25
Z	16MS4SOS46502 -- 26

Expected output:-

Partner_Name	Inward_no &' -- '&RowNo()
A	16MS2ADHC7838 -- 1
B	16MS3SOS34969 -- 2
C	16MS3SOS36890 -- 3
D
E
F	16MS3SOS48772 -- 4
G	16MS3TRB37584 -- 5
H	16MS3TRB40266 -- 6
I	16MS4ADHC6867 -- 7
J	16MS4ADHC6902 -- 8
K	16MS4ADHC6951 -- 9
L	16MS4ADHC7835 -- 10
M	16MS4ADHC7842 -- 11
N	16MS4DEM48171 -- 12
O	16MS4PPNC7830 -- 13
P	16MS4SOS40478 -- 14
Q	16MS4SOS40871 -- 15
R	16MS4SOS43214 -- 16
S
T
U
V
W	16MS4SOS46341 -- 17
X	16MS4SOS46500 -- 18
Y	16MS4SOS46501 -- 19
Z	16MS4SOS46502 -- 20

Regards

Mahesh

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:02:05 GMT

Hi Experts,

Help me on this

jagan‌loveisfail‌avinashelite‌vinieme12 stalwar1‌

Regards

Mahesh

Re: Rowno() count

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:07:59 GMT

May be like below

LOAD If(Len(Inward_no)>0,Inward_no &' -- '&RowNo(),' ') as Inward_no,

Partner_Name

FROM

[Req.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Rowno() count

prat1507 — Wed, 21 Jun 2017 06:10:51 GMT

Hi Mahesh

Please use

T1:

LOAD

RowNo() as Row,

if(Inward_no='','',Inward_no &' -- '&RowNo()) as Inward_no_RowNo,

Partner_Name

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:12:51 GMT

Not working Anil

I want to calculate in front end

Regards

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:13:58 GMT

Hi Pratyush,

Not working.

Regrds

Re: Rowno() count

avinashelite — Wed, 21 Jun 2017 06:20:11 GMT

Try like this

LOAD if(len(trim(Inward_no))>0,Inward_no&'-'&AutoNumber(Inward_no)) as Row,

Partner_Name

FROM

[My Documents\Req.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Rowno() count

prat1507 — Wed, 21 Jun 2017 06:21:35 GMT

Hi Mahesh

PFA the app with the desired results.

Regards
Pratyush

Re: Rowno() count

avinashelite — Wed, 21 Jun 2017 06:21:37 GMT

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:27:45 GMT

Hi All,

Here i am attaching Original IP/OP and QVW,

I want calculate in Front end level

Regards

Mahesh

Re: Rowno() count

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:28:28 GMT

Front end doesn't have this feature. Why you want in Front end only rather we have in script

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:28:56 GMT

Hi Brother,

Just now i attached original files please look at that

Regards

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:30:02 GMT

Hi Anil,

Please look at my recent attached original files

Regards

Re: Rowno() count

prat1507 — Wed, 21 Jun 2017 06:30:18 GMT

Hi Mahesh

Can you plz be a little clear as to what you desire for.

Regards

Pratyush

Re: Rowno() count

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:33:04 GMT

I don't see any change in your original reply. Do you have license, If so open my file and see how it works

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:41:38 GMT

In my attachment "Serial" column data like T-FPA-1/10 ------ T-FPA-23/10

Here rowno() calculating the blank rows also in this column, but i don't want to calculate Blank in Serial column data like below

T-FPA-1/10 ------ T-FPA-10/10

Regards

Re: Rowno() count

qlikview979 — Wed, 21 Jun 2017 06:43:00 GMT

Hi Anil

In my attachment "Serial" column data like T-FPA-1/10 ------ T-FPA-23/10

Here rowno() calculating the blank rows also in this column, but i don't want to calculate Blank in Serial column data like below

T-FPA-1/10 ------ T-FPA-10/10

Regards

Re: Rowno() count

avinashelite — Wed, 21 Jun 2017 06:47:27 GMT

I don't see any null values in your data for Serial , VIN number etc ...

what is the output your excepting ??

Re: Rowno() count

sasikanth — Wed, 21 Jun 2017 06:52:02 GMT

HI Mahesh

Please check the below change in SERIAL exp

if(aggr(Sum({<[VIN Number]=>}[Gross Amount]),[Partner Name])-aggr(Sum({<[VIN Number]=>}Deduction),[Partner Name])>0,

Serials&'-'& aggr(RowNo(TOTAL),[Partner Name]) &'\'&count( DISTINCT TOTAL [Partner Name]))

and in sort tab for Partner name use exp sort ascending

=Serials&'-'&RowNo().

PFA for reference

Re: Rowno() count

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:52:43 GMT

Bro,

He is expecting similar which we did earlier. But he is looking in Front end. Can you see his report while you open the qvw. There you will get serial as expression. From that he want to apply this condition Autonumber(). But, as i aware we don't have option called Autonumber() on front end. He is expecting seq. numbers value for it like 4,5,6,7,...

I have notice, Here we require aggr for that

if(aggr(Sum({<[VIN Number]=>}[Gross Amount]),[Partner Name])-aggr(Sum({<[VIN Number]=>}Deduction),[Partner Name])>0,

Serials&'-'& Aggr(RowNo(TOTAL), [Partner Name])&'\'&count( DISTINCT TOTAL [Partner Name]))