Complex Logic

[smilingjohn]

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

[swuehl · Accepted solution]

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,

     ID

FROM

[Test1 (2).xlsx]

(ooxml, embedded labels, table is Sheet1)

;

[smilingjohn]

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

[swuehl]

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.

[OmarBenSalem]

Just add a new field '(ReleaseNumber):

LOAD

    "Hours",

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

    Release,

    Status,

    ID

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:

[smilingjohn]

This is working Stefan

Thanks a lot .