How to create a new column of data based on other columns (in the script)

[#1]

Hello,

I was hoping someone could help with the following:

I have attached sample data in an Excel file to show what I'd like to do.

I have a set of files (about 20,000 rows worth) with four columns for four different staff shifts (First_Shift, Second_Shift, Third_Shift and Fourth_Shift).  Each column only has "data" (in text form) denoting if a staff member was utilized during that shift for that particular file date.  If no staff was used, then the cell is left blank.

I would like to create an additional column that would aggregate all four shifts and total the number of "staff" used for that row (file date); each cell with content just means one staff member for that shift.

I would also like to see if this could be done on the load script.

Thank you in advance for the help!

Karen

[MarcoWedel]

LOAD
    *,
    if(Len(First_Shift)>0,1,0)+if(Len(Second_Shift)>0,1,0)+if(Len(Third_Shift)>0,1,0)+if(Len(Fourth_Shift)>0,1,0) as Staff_Involvement;
LOAD Date(File_Date, 'DD.MM.YYYY') as File_Date,
     Patient_Name,
     First_Shift,
     Second_Shift,
     Third_Shift,
     Fourth_Shift
FROM
[http://community.qlik.com/servlet/JiveServlet/download/491673-97745/Staff%20File.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 18)),
Remove(Row, Pos(Top, 17)),
Remove(Row, Pos(Top, 16))
));

Hi Karen,

this script should create your desired table.

hope this helps

regards

Marco

[veidlburkhard · Accepted solution]

Hi Karen,

attached please find a possible solution.

Regards

Burkhard

[sudeepkm]

Please find the attachment and see if this is something you want.

[#5]

Thank you both, this is just what I needed and it works.

[MarcoWedel]

you're welcome.

Glad I could help

regards

Marco