topic Re: Complex Logic in QlikView

Complex Logic

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

Hi Expertise

Please find the attachment of QVW file and csv file .

I am trying to show a bar graph for release by hours .

Hear i need to show only those whose status is completed or accepted .

Everything is working fine ..

But if you observer the release number R-18.9 is showing 78 hours ..which actually should not be displayed .

My requirement is like if any release number has a status other than completed or accepted it should not be displayed in the bar graph .

if you refer the Excel file ...and filter it by 18.9 data under status i have the status defined and in progress in such case the r-18.9 release should not be shown.

as shown in the below scrceen shot the release 18.9 has a status of defined and in progress

Therefore this release should be neglected

only i need to show the releases which has complete status either accepted or completed .

how can i handle this at the script level ?

Please some one help me on this .....

Thanks in advance

Re: Complex Logic

swuehl — Tue, 28 Aug 2018 10:01:23 GMT

If you want to do it in the script, try to create a list of your release names that you don't want to retrieve and then use a WHERE NOT EXISTS() filter for the details:

Filter:

LOAD if(WildMatch(Release,'18.6*'),'R-18.6',

if(WildMatch(Release,'18.7*'),'R-18.7',

if(WildMatch(Release,'18.8*','18.08 T2*'),'R-18.8',

if(WildMatch(Release,'18.9*','18.09 T2*'),'R-18.9',

if(WildMatch(Release,'18.10*'),'R-18.10',

if(WildMatch(Release,'18.11*'),'R-18.11')))))) as Release2Exclude

FROM

[Test1 (2).xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE not match(Status,'COMPLETED','ACCEPTED');

Test:

LOAD *

where not exists(Release2Exclude, Release_Name);

Test:

LOAD Hours,

Code,

Release,

if(WildMatch(Release,'18.6*'),'R-18.6',

if(WildMatch(Release,'18.7*'),'R-18.7',

if(WildMatch(Release,'18.8*','18.08 T2*'),'R-18.8',

if(WildMatch(Release,'18.9*','18.09 T2*'),'R-18.9',

if(WildMatch(Release,'18.10*'),'R-18.10',

if(WildMatch(Release,'18.11*'),'R-18.11'))))))as Release_Name,

Status,

FROM

[Test1 (2).xlsx]

(ooxml, embedded labels, table is Sheet1)

;

Re: Complex Logic

smilingjohn — Tue, 28 Aug 2018 10:12:00 GMT

Hi Stefan ,

Thanks for the reply

I tried to run the script you suggested above..

but this is not working ...

I want to skip the whole release when the status is something else than completed or accepted ..

The data will be dynamic ...

Thanks

Re: Complex Logic

swuehl — Tue, 28 Aug 2018 10:28:28 GMT

What do you mean with not working, can you give an example where the output is not as expected?

Attached the QVW with the updated data.

Re: Complex Logic

OmarBenSalem — Tue, 28 Aug 2018 10:36:02 GMT

Just add a new field '(ReleaseNumber):

LOAD

"Hours",

SubField(Release,' ',1) as ReleaseNumber,

Release,

Status,

FROM [lib://downloads/Test1.xlsx]

(ooxml, embedded labels, table is Sheet1);

and use this expression:

if(count( total <ReleaseNumber>distinct Status) <=2 and (Status='COMPLETED' or Status='ACCEPTED'), sum(Hours))

Result:

Re: Complex Logic

smilingjohn — Tue, 28 Aug 2018 10:42:07 GMT

This is working Stefan

Thanks a lot .