Converting the source data format after Load

[sayadutt]

Hello Team,

I am loading the data from an a SharePoint list which is of the following format

Division      Application     Functional     Smoke     Regression

aaa                   123               Yes          Yes          No

aaa                   345               Yes          No           No

bbb                   567               Yes          Yes          No

ccc                    999               No           No          No

Now my requirement is to change  to the below desired format in the Edit Script section

Division      Application     TestingArea

aaa                   123               Functional         

aaa                    123              Smoke

aaa                   345               Functional     

bbb                   567               Functional     

bbb                   567               Smoke

ccc                   999               None

Basically, when the value is "Yes" in Functional, Smoke or Regression, we add it under TestingArea

If All(Functional, Smoke, Regression) are No, add "None" under TestingArea.

Can you please help me in achieving this,

thanks

[swuehl · Accepted solution]

Something like

CROSS:

CROSSTABLE (TestingArea, Value, 2)

LOAD Division,

          Application,

          Functional,

          Smoke,

          Regression,

          If (Functional='No' and  Smoke='No' and Regression ='No', 'Yes','No' ) as None

FROM YourTable;

NOCONCATENATE

LOAD Division, Application, TestingArea

RESIDENT CROSS

WHERE Value = 'Yes';

DROP TABLE CROSS;