Get all possible parent folders for a given folder location

[#1]

Hi,

I have a rather large table (5m+ rows)  with unique folder locations e.g. C:\Windows\Tmp\30102014

I need to create another table with a row for every possible parent folder. So for the example above I would have 3 entries

Child, Parent

C:\Windows\Tmp\30102014, C:

C:\Windows\Tmp\30102014, C:\Windows

C:\Windows\Tmp\30102014, C:\Windows\Tmp

I've tried looping through the tables, but it takes far too long.

Would there be a QlikView function for this which I am not thinking of?

[swuehl · Accepted solution]

You can use a LOAD with a WHILE clause:

LOAD *,

           Left(Folder,index(Folder,'\',iterno())-1) as Parent

WHILE iterno()<=SubstringCount(Folder,'\');

LOAD * INLINE [

Folder

C:\Windows\Tmp\30102014

];