topic Re: do/for loop script needed? in QlikView

do/for loop script needed?

Thu, 23 Apr 2015 22:31:18 GMT

Hi All,

Please help me implement below requirement.

Data looks like below

Number	Sub-Number	Status
1	1.1	pass
1	1.2	fail
1	1.3	reject
2	2.1	pass
2	2.5	pass
3	3.2	fail
3	3.3	fail
3	3.4	reject

Requirement:

get the list of numbers for which, status is only 'pass' for all the Sub-Number.

to make it clear, if corresponding status for all the Sub-Numbers for a given Number is ''pass'', then only it should appear in output list.

so in above example, output list will contain only number 2.

please help.

any suggestion appreciated.

Thanks&Regard

Jyothi

Re: do/for loop script needed?

maxgro — Thu, 23 Apr 2015 23:09:40 GMT

make a straight table chart with

dimension

Number

expression

if(Count(TOTAL <Number> Status) = Count({$ <Status={pass}>} TOTAL <Number> Status),1, 0)

Re: do/for loop script needed?

anbu1984 — Fri, 24 Apr 2015 05:55:19 GMT

In Script,

T1:
Load * Inline [
Number,Sub-Number,Status
1,1.1,pass
1 ,1.2,fail
1,1.3,reject
2,2.1,pass
2,2.5,pass
3,3.2,fail
3,3.3,fail
3,3.4,reject ];

Load Number where IsStatusPass = 1;
Load Number,If(MaxString(Status)='pass' And MaxString(Status)=MinString(Status),1,0) As IsStatusPass Resident T1 Group By Number;

Re: do/for loop script needed?

Fri, 24 Apr 2015 13:38:02 GMT

Hi Massimo,

Thanks for you suggestion. But requirement is to implement it in Script..

Re: do/for loop script needed?

Anonymous — Fri, 24 Apr 2015 13:52:09 GMT

Or this:

T1:
Load * Inline [
Number,Sub-Number,Status
1,1.1,pass
1 ,1.2,fail
1,1.3,reject
2,2.1,pass
2,2.5,pass
3,3.2,fail
3,3.3,fail
3,3.4,reject ];

Result:
LOAD Number where AllStatus='pass';
LOAD DISTINCT Number, concat(distinct Status) as AllStatus RESIDENT T1
GROUP BY Number;

DROP TABLE T1;

Re: do/for loop script needed?

maxgro — Fri, 24 Apr 2015 17:12:23 GMT

in script, if your table is T1

T2:

load Number, Status where Status = 'pass';

NoConcatenate load Number, count(distinct Status), MaxString(Status) as Status

Resident T1 group by Number;

DROP Table T1;

Re: do/for loop script needed?

Fri, 24 Apr 2015 17:13:53 GMT

Perfect! This worked for me.

Thanks a lot Anbu.

Would you please explain your code? I m new to Qlikview so having trouble to understand..

Re: do/for loop script needed?

anbu1984 — Mon, 27 Apr 2015 10:01:25 GMT

If you have only Pass for a Number, then your MaxString and MinString of Status will be Pass and IsStatusPass is set to one. In Preceding load, where clause is used to get Numbers only have Pass as status

Load Number where IsStatusPass = 1; //Preceding Load

If(MaxString(Status)='pass' And MaxString(Status)=MinString(Status),1,0) As IsStatusPass

Re: do/for loop script needed?

Tue, 28 Apr 2015 11:27:13 GMT

Thanks Anbu!

Is it possible if i have to load many fields in the above script?

i tried adding one more field.. and i m getting Unknown Error.

could you please suggest?

Thanks

Jyothi.

Re: do/for loop script needed?

anbu1984 — Tue, 28 Apr 2015 12:20:34 GMT

Do you want to add another field along with Number? Then you should include the same in Group By clause

Can you post what you tried?

Re: do/for loop script needed?

Tue, 28 Apr 2015 12:24:13 GMT

wow! it worked.

so if i want to add 5 fields, i should be adding all 5 fields in Group By clause is it?

I hope it wont impact the result

Thanks a lot Anbu!

Re: do/for loop script needed?

anbu1984 — Tue, 28 Apr 2015 12:27:53 GMT

Yes. If you are not getting correct result, then post the script you tried and expected output