Get all possible parent folders for a given folder location

Fri, 20 Nov 2020 18:02:30 GMT

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?

Re: Get all possible parent folders for a given folder location

swuehl — Fri, 30 Oct 2015 21:50:01 GMT

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

];