topic Re: Create Field and value in Script in QlikView

Create Field and value in Script

cbaqir — Sat, 22 Aug 2020 15:56:44 GMT

I need to create a new dimension based on this logic below. One workstation can have many test cases with different statuses. How do I replace the set analysis correctly to be used in the script?

For each workstation
If all testcase status = null then workstation status = not tested
else
If any testcase status = failed then workstation status = failed
Else if any testcase status = null then workstation status = incomplete
Else if any testcase status = ready for retest then workstation status = ready for retests
Else (all test case status = passed) then workstation status = passed

~~~~~~~~~~~~~~~~

LOAD Department,
Workstation,
[Test Case],
Workstation & '-' & [Test Case] as %KeyWorkstation_TC,
if(isnull( Status),'Not Tested', Status) as Status,

if(not match(Status,'Failed','Passed','Ready to Retest'),'Not Tested',
if(not match(Status,'Failed','Ready to Retest','Incomplete'),'Passed',
if(count({<Status*={"Failed"}>} distinct Workstation)>=1,'Failed',
if (count({<Status={"Not Tested"}>} distinct Workstation)>=1,'Incomplete',
if(count({<Status*={"Ready to Retest"}>} distinct Workstation)>=1,'Ready to Retest','Other'
))))), as WORKSTATION_STATUS,

Timestamp,
User,

[Failure Reason],
//[Mapped Printer],
Comments
FROM
[..\External_Data\TDR 8-19 Test Results EPIC.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Create Field and value in Script

rubenmarin — Sun, 23 Aug 2020 09:13:08 GMT

Hi, you can't use set analysis in script. Also to use aggregation functions like Count() you need a Group By.

I will try to assign the workstation status in next steps, as an example:

// mapping to assign a code to each status (null will be zero) mapStatus: Mapping LOAd * Inline [ Status, Code Failed, 1 Ready to Retest,2 Passed, 3 ] // Load data and assign number to status Data: LOAD Department, ..., Applymap('mapStatus', Status, 0) as codStatus FROM ...; // Identify 'Not Tested' chkNotTested: LOAD Workstation as chkNotTested Where MaxValue=0; LOAD Workstation, Max(codStatus) as MaxValue Resident Data Group By Workstation; // Set status to each workstation // This table can be joined with Data table or leave it as independent table WorkstationStatus: LOAD Workstation If(Exists('chkNotTested', Workstation), 'Not Tested' ,If(MinValue=0, 'Incomplete' ,If(MinValue=1, 'Failed' ,If(MinValue=2, 'Ready for Retest' ,If(MinValue=3, 'Passed', 'Other (recheck logic)' ))))) as WorkstationStatus; LOAD Workstation Min(codStatus) as MinValue Resident Data Group By Workstation; DROP Table chkNotTested;

The code is not testedm maybe there is a typo or something to fix but that's the idea.

Re: Create Field and value in Script

cbaqir — Mon, 24 Aug 2020 14:28:30 GMT

Thanks. This is great but I am seeing a discrepancy when I look at one I see as Failed and this logic says incomplete. Failed should have a higher precedence. Any thoughts?

Re: Create Field and value in Script

Brett_Bleess — Thu, 03 Sep 2020 15:36:41 GMT

@rubenmarin Hey Ruben, apologies for the notification issues we have been having, just wanted to flag this one for you.

Cheers,
Brett

Re: Create Field and value in Script

rubenmarin — Thu, 03 Sep 2020 19:17:56 GMT

@Brett_Bleess, thanks for the advise, I wasn't aware. Another Issue I'm finding a lot is that an answers returns an error when posting, and the full post is lost, no option to retrieve. this is the 2nd time I'm answering this post.

@cbaqir, a possible fix:

WorkstationStatus: LOAD Workstation If(Exists('chkNotTested', Workstation), 'Not Tested' ,If(hasFailed, 'Failed' ,If(MinValue=0, 'Incomplete' ,If(MinValue=2, 'Ready for Retest' ,If(MinValue=3, 'Passed', 'Other (recheck logic)' ))))) as WorkstationStatus; LOAD Workstation Min(codStatus) as MinValue, If(Index(Concat(codStatus), '1')>0, 1, 0) as hasFailed Resident Data Group By Workstation;