Union 2 columns (Not in script)

[#1]

Hello

Attached excel. I am trying to union 'Dr1', 'Dr2' and 'Dr3' to one column (not distinct). The Column B/N1, B/N2, B/N3 is the Dr answers.

The last column B/N is the correct answer.

I am comparing the Dr answer and the correct answer,

So Union table should look like this (2 columns):

rob  Yes

joe  Yes

max  Yes

rina  No

joe  Yes

rob  No

Nuna  No

mike  Yes

joe  Yes

rina  Yes

joe  Yes

mike  Yes

After the union i am going to count each name and the number of the correct answers, its gonna look like this (2 columns):

rob  1

joe  4

max  1

rina  1

mike  2

I don't want to do it in the script because I am going to have problems in filtering after. am i right?

Thank you for helping!

[vinieme12]

You will need to do it in script

LOAD [case num],

     [Dr 1] as DR,

     [B/N 1] as Answered,

     [B/N] as CorrectAnswer,

     'DR1' as DR_Instance,

     if([B/N 1] = [B/N],1,0) as ResponseFLAG

FROM

(ooxml, embedded labels, table is Sheet1);

concatenate

LOAD [case num],

     [Dr 2],

     [B/N 2],

     [B/N] as CorrectAnswer,

  'DR1' as DR_Instance,

      if([B/N 1] = [B/N],1,0) as ResponseFLAG

FROM

(ooxml, embedded labels, table is Sheet1);

concatenate

LOAD [case num],

     [Dr 3] as DR,

     [B/N 3] as Answered,

     [B/N] as CorrectAnswer,

     'DR1' as DR_Instance,

          if([B/N 1] = [B/N],1,0) as ResponseFLAG

FROM

(ooxml, embedded labels, table is Sheet1);

For Expression use Sum (ResponseFLAG)

[petter · Accepted solution]

Doing the correct transformations in your load script makes it much easier to get exactly what you want in your dashboard:

[#4]

Thank you Petter!