Straight table if() statements

[#1]

Hello all, this is my first post and I am fairly new to QlikView so please bear with me.

I have a set of information illustrated by the table below, I have labelled them A, B & Boolean so hopefully the answer will apply to more people than just my specific case.

Field A	Field B	Boolean
A1	B1	1
A1	B2	0
A1	B3	0
A1	B4	1
A2	B1	1
etc	etc	etc

In my application though, Field A is basically a project number, Field B is a project phase and the boolean is whether or not that phase is complete.

Basically what I need is to be able to say 'If B2 is not complete AND B1 is complete then B2 is due' So that the output would look something like:

Field A	Field B	Is it Due
A1	B2	Due
etc	etc	etc

I thought something like:

=if(FieldB='B2' AND Boolean = 0, if(FieldA='B1' AND Boolean = 1, 'Due', 'Not Due'))

But I think I'm on completely the wrong track, do I need to load the information differently?

Hopefully this is clear enough, and I'm not asking too stupid a question

[PrashantSangle]

Hi,

I am no sure that i am understand clearly but as you say,

"If B2 is not complete AND B1 is complete then B2 is due"

I means you are evaluating value for FieldB only because there is no B2 in fieldA and You have to compare with your previous field only

Then use previous() in your script like,

if(FieldB='B2' AND Boolean = 0, if(Previous(FieldB)='B1' AND Previous(Boolean) = 1, 'Due', 'Not Due'),'Not Due') as [Is it Due],

Hope this will help you,

Regards,

[menta]

You can try something like this

T:

LOAD * Inline

[

FieldA,FieldB,Boolean

A1, B1, 1

A1, B2, 0

A1, B3, 0

A1, B4, 1

A2, B1, 1

etc, etc, etc

]

;

load

FieldA,

FieldB,

Boolean,

if(Peek(FieldA)=FieldA, if(Peek(Boolean)=1 and Boolean=0,'Due','Not due') ) as FlagNew,

if(Boolean = 0,'Due','Not Due') as Flag

Resident T;

DROP Table T;

[#4]

I think something like this should resolve your question:-

aggr(if(Boolean=0,'Due','Complete'),FieldB,FieldA)

[SunilChauhan]

may be you looking this

=if(FieldB='B2' AND Boolean = 0,'Due', if(FieldA='B1' AND Boolean = 1,'Due' , 'Not Due'))

hope this helps

[#6]

Just looking at the documentation for aggr() and I think this is probably where I should be looking. So thanks for this!

The example you posted isn't quite right but it definitely seems like a step in the right direction

I will look into this further, if it solves it I will let you know

[#7]

This seems to be evaluating based on Field A being B1 at some stage. This would never be the case. Perhaps seeing the data like this might help:

Proj #	Phase	Status
1001	PAY1	1
1001	PAY2	0
1001	PAY3	0
1002	PAY1	1
1002	PAY2	1

When PAY1 is complete for a project, PAY2 is due. But PAY3 is not due until PAY2 is complete.

So I want to add a column using an expression to get the below effect:

Proj #	Phase	Status	Due
1001	PAY1	1	-
1001	PAY2	0	Due
1001	PAY3	0	-
1002	PAY1	1	-
1002	PAY2	1	-

[#8]

I seem to be nearly there, using the aggr() function using the above field names I have the following:

aggr(if(Boolean = 0 AND above(Boolean) = 1, 'Due', '-')FieldA, FieldB)

I'm going to mark this as correct, I have some issues with my data now but I think they are unique to my data so irrelevant to the community

Thanks for everyone's help big or small

[rwunderlich · Accepted solution]

I'm not sure why you need the aggr(). If you make the dimensions FieldA & FieldB then:

=if(NOT Boolean AND Above(Boolean), 'Due', '')

-ob

[#10]

Yes of course, how stupid of me. While looking into aggr() I found above(), and just used it in what I already had. Rather than rethinking it!!

Thanks!