topic Flag the line that doesn't meet a conditon in QlikView

Flag the line that doesn't meet a conditon

Anonymous — Tue, 12 Apr 2016 16:00:31 GMT

Hey fellow Qlikkers,

I got another one today that I'm having a hard time with in the script. I want to flag the below highlighted field with a 1. The reasoning behind it is that the line item only has an 'E' in the last column. I need this for both situations, whether the line item only has an 'E' or a 'Q'. We'll want to toss lines out that have both Es and Qs. The flag of 1 is only needed for those lines that don't have both.

I've attached the excel sheet and a qvw. Thanks a lot! I may have a backup plan if we can't get this one.

Re: Flag the line that doesn't meet a conditon

swuehl — Tue, 12 Apr 2016 16:03:29 GMT

If you only have two categories,try something like

LEFT JOIN (YourTable)

LOAD

If(Count(DISTINCT PO_History_Cat) =1,1) as Flag

PO#,

PO_Item

RESIDENT YourTable

GROUP BY PO#, PO_Item;

Re: Flag the line that doesn't meet a conditon

sunny_talwar — Tue, 12 Apr 2016 16:06:34 GMT

Try this:

BYAH:

LOAD PO#,

PO_Item,

AcctDoc,

AcctLine,

DollarValue,

DollarValueAcct,

PO_History_Cat

FROM

FlagTheLine.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (BYAH)

LOAD PO_Item,

If(Count(DISTINCT PO_History_Cat) = 1, 1, 0) as Flag

Resident BYAH

Group By PO_Item;

Re: Flag the line that doesn't meet a conditon

Anonymous — Tue, 12 Apr 2016 17:36:09 GMT

Thanks, Sunny!

Re: Flag the line that doesn't meet a conditon

Anonymous — Tue, 12 Apr 2016 17:43:41 GMT

i feel like PO# is def needed in the load here for use with the entire dataset.

Re: Flag the line that doesn't meet a conditon

sunny_talwar — Tue, 12 Apr 2016 17:45:06 GMT

I agree, since your data only had one PO#, i missed to include it, but real data may require you to add PO# to make the row line unique for performing the count