do/for loop script needed?

[#1]

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

[maxgro]

make a straight table chart with

dimension         

     Number

expression          

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

[anbu1984 · Accepted solution]

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;

[#4]

Hi Massimo,

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

[Anonymous]

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;

[maxgro]

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;

[#7]

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..

[anbu1984]

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

[#9]

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.

[anbu1984]

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?