Create Field and value in Script

[cbaqir]

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);

[rubenmarin1]

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.

[cbaqir]

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? 

[Brett_Bleess]

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

Cheers,
Brett

[rubenmarin1]

@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;