Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Meghann_MacDonald

Tracking Folder Structure with Onedrive Metadata

Hi all, I am using onedrive metadata to recreate my folderstructure. I started with this:

[tempFiles]:
LOAD name as [Items.name],
parentReference_path as [Items.parentReference_path],
folder_childCount as [Items.folder_childCount],
webUrl as [Items.webUrl];

SELECT name,
parentReference_path,
folder_childCount,
webUrl
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Nick',
maxResults=''
);

Which provides me with a list of the folder names within the first folder, "Nick". Great!

Now, based on that list, I want to have it look inside EACH folder and give me the names of THOSE files/folders. I think it would be basically the same code as above except with a "path" that is a variable, assigned to the file names from the table above. 

 

Additional info: this is what the code above gives me: 

Music.png

These "Item Names" are the names of subfolders. i then want to generate a list of the 4 folders within the Matthew_court_cmaj folder, and then the 3 folders within TalkTooMuchProject folder, and so on. 

Happy Holidays!

Labels (4)
1 Solution

Accepted Solutions
Meghann_MacDonald
Author

Hi @ksypolit, yes I think I found a way. You can use variables in your select statements

You will load the first hierarchy to find the folders in your desired starting folder like this: 

LIB CONNECT TO 'OneDrive_Metadata - 'your credentials'';

Filelist:
LOAD id as [Items.id],
name as [Items.name],
parentReference_path as [Items.parentReference_path],
right(parentReference_path, len(parentReference_path)-13) as [Folder],
if(len(folder_childCount)>0, 'Folder', 'File') as [Items.type];

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation',
maxResults=''
);

Keep in mind you can remove my "AS" statements, the are just there to help me start to build the hierarchy. This gives you a table listing all of the items in the first Ideation folder, and also lets you know if they are files or folders. 

Then you use the following to create a variable 'x' with all of the item names from the list above (for the folders). 

FOR x = 0 TO NoOfRows('Filelist') - 1
LET vItem = peek('Items.name', $(x), 'Filelist');

Then, you load another list with the same info, but with the variable as the path: 

Filelist2:
LOAD id as [id],
name as [name],
parentReference_path as [referencepath],
right(parentReference_path, len(parentReference_path)-12) as [path],
if(len(folder_childCount)>0, 'Folder', 'File') as [type],
subfield(parentReference_path, '/', 4) as Folder;

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation/$(vItem)',
maxResults=''
);
NEXT x

And this gives you another table with every file or folder located in one of the folders that lies within the first Ideation folder. You basically repeat this with a new variable each time. I'm sure there is a way to just have this run over and over and concatenate into one table as well, rather than filelist1, filelist2, filelist 3 etc. Especially if you just changed to "let vItem = peek('Itemsname' where type = folder). Because if you have a list of all folders and their paths, then all items and their path, you can just take the paths and break them down with subfields to build the hierarchy. I didn't get that far. Hope this helps!

View solution in original post

3 Replies
ksypolit
Contributor II
Contributor II

Hello,

have you find a solution for your questions.

I want to do exactly the same thing.

 

Meghann_MacDonald
Author

Hi @ksypolit, yes I think I found a way. You can use variables in your select statements

You will load the first hierarchy to find the folders in your desired starting folder like this: 

LIB CONNECT TO 'OneDrive_Metadata - 'your credentials'';

Filelist:
LOAD id as [Items.id],
name as [Items.name],
parentReference_path as [Items.parentReference_path],
right(parentReference_path, len(parentReference_path)-13) as [Folder],
if(len(folder_childCount)>0, 'Folder', 'File') as [Items.type];

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation',
maxResults=''
);

Keep in mind you can remove my "AS" statements, the are just there to help me start to build the hierarchy. This gives you a table listing all of the items in the first Ideation folder, and also lets you know if they are files or folders. 

Then you use the following to create a variable 'x' with all of the item names from the list above (for the folders). 

FOR x = 0 TO NoOfRows('Filelist') - 1
LET vItem = peek('Items.name', $(x), 'Filelist');

Then, you load another list with the same info, but with the variable as the path: 

Filelist2:
LOAD id as [id],
name as [name],
parentReference_path as [referencepath],
right(parentReference_path, len(parentReference_path)-12) as [path],
if(len(folder_childCount)>0, 'Folder', 'File') as [type],
subfield(parentReference_path, '/', 4) as Folder;

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation/$(vItem)',
maxResults=''
);
NEXT x

And this gives you another table with every file or folder located in one of the folders that lies within the first Ideation folder. You basically repeat this with a new variable each time. I'm sure there is a way to just have this run over and over and concatenate into one table as well, rather than filelist1, filelist2, filelist 3 etc. Especially if you just changed to "let vItem = peek('Itemsname' where type = folder). Because if you have a list of all folders and their paths, then all items and their path, you can just take the paths and break them down with subfields to build the hierarchy. I didn't get that far. Hope this helps!

ksypolit
Contributor II
Contributor II

Hi @Meghann_MacDonald 

Thank you very much for the reply and the code.

Yes it works great and reads all subfolders and their content. I will try to make some changes to get the subfolders of all depth levels as you mention.

Thanks again