Need to relate one value in a field to multiple values in the same field

[#1]

I am attaching both a sample excel sheet and Qlikview file for this question. In the script I have created a table using raw data from the excel file to create a multi-header pivot table. In the excel file, there is the Project/Resource column where there are both the name of the person and underneath the projects they are working on. Some share the projects since there can be multiple people on one project at a time. Column B is the identifier I created with VLOOKUP to distinguish between the name and project.

What I need is to associate the name of the person with the following projects they are working on underneath. I was thinking of creating a new column somehow in the Result Table that I created in the script. This field could be called Name. Somehow if it read that if column B equals resource, it could enter the name into the 'Name' new field. And it would keep looping and entering the name until it came across resource again in column B and this would repeat until all the end of the table.

I'm not exactly sure how this can be accomplished in the script so some help would be greatly appreciated! I had used code from thismulti_header_pivot_import.qvw . If you have any questions please feel free to reach out!

[MarcoWedel · Accepted solution]

Hi,

one possible solution to create the Name field could be:

table1:
LOAD RecNo() as ID,
     [Project/Resource], 
     [Project/Resource Identifier],
     If([Project/Resource Identifier]='Resource',[Project/Resource],Peek(Name)) as Name
FROM [https://community.qlik.com/servlet/JiveServlet/download/1274894-279879/SampleExcel.xlsx] (ooxml, embedded labels, table is Main, filters(Remove(Row, Pos(Top, 1))));

hope this helps

regards

Marco

[#3]

thank you! works great for that!

[MarcoWedel]

you're welcome

glad it helped

regards

Marco