Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Karen,
attached please find a possible solution.
Regards
Burkhard
Please find the attachment and see if this is something you want.
Thank you both, this is just what I needed and it works.
you're welcome.
Glad I could help
regards
Marco