Excel join in worksheet where data is grouped

[Anonymous]

Good day,

     I have a workbook that I would like to join (ETN) based on one field in a worksheet.  The format within the sheet is grouped so the related information appears to the typical join as blank.  My guess is I need some scripting upon the load, but not sure how to do it.  I have attached an example of a limited amount of data.  Any thoughts or ideas would be much appreciated.

[Gysbert_Wassenaar]

See attached qvw. Is that what you need?

[Anonymous]

unfortunately I don't have purchased licence yet so I can' see your script in Qlikview

[Nicole-Smith · Accepted solution]

Data:

LOAD if(len([1])>0,[1],peek('1')) as [1],

     if(len([2])>0,[2],peek('2')) as [2],

     if(len([3])>0,[3],peek('3')) as [3],

     if(len([6])>0,[6],peek('6')) as [6],

     if(len([ETN])>0,[ETN],peek('ETN')) as [ETN]

FROM hierarchy.xlsx

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

This is what I needed.  Thank you all very much