Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Tags (1)
1 Solution

Accepted Solutions
veidlburkhard
Contributor III

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

Hi Karen,

attached please find a possible solution.

Regards

Burkhard

5 Replies

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

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

QlikCommunity_Thread_111324_Pic1.png

QlikCommunity_Thread_111324_Pic2.png

veidlburkhard
Contributor III

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

Hi Karen,

attached please find a possible solution.

Regards

Burkhard

sudeepkm
Valued Contributor III

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

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

Not applicable

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

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

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

you're welcome.

Glad I could help

regards

Marco

Community Browser