Hello experts,
I'm using a FOR/NEXT to identify files and file sizes across our servers, which concatenates onto an inline table. Another resident table then makes this pretty for use in a dashboard. All tables are dropped at the end of each iteration.
Full Script below.
The loop runs fine for the first iteration. However on the second, the inline table fails to load as it is still trying to load fields present in a table that was dropped in the previous iteration.
Error Log on second iteration:
08/10/2019 10:21:03: 0248 Table_2:
08/10/2019 10:21:03: 0249 NOCONCATENATE
08/10/2019 10:21:03: 0250 LOAD 1 INLINE [
08/10/2019 10:21:03: 0251 Name
08/10/2019 10:21:03: 0252 ]
08/10/2019 10:21:03: 9 fields found: Server, File, File Type, File Type Category, Folder Level 1, Folder Level 2, Folder Level 3, Folder Level 4, File Size MB, General Script Error
08/10/2019 10:21:03: Execution Failed
08/10/2019 10:21:03: Execution finished.
Equivalent part of loop on first iteration:
08/10/2019 10:10:23: 0172 Table_1:
08/10/2019 10:10:23: 0173 NOCONCATENATE
08/10/2019 10:10:23: 0174 LOAD 1 INLINE [
08/10/2019 10:10:23: 0175 Name
08/10/2019 10:10:23: 0176 ]
08/10/2019 10:10:23: 1 fields found: 1, 0 lines fetched
-----
LET v_GB_to_MB = 1000;
// SUBROUTINE FOR GETTING FILE INFORMATION
SUB DoDir (Root)
FOR EACH File in FILELIST (Root&'\*')
CONCATENATE($(v_TableName))
Load
'$(File)' as Name,
FILESIZE( '$(File)' ) as Size
AUTOGENERATE 1;
NEXT File
FOR EACH Dir in DIRLIST (Root&'\*' )
CALL DoDir (Dir)
NEXT Dir
END SUB
// CALL SUBROUTINE FOR EACH SERVER
FOR v_ServerLoop = 1 to 1
LET v_TableName = 'Table_'&'$(v_ServerLoop)';
$(v_TableName):
NOCONCATENATE
LOAD * INLINE [
Name, Size
];
// DETERMINE NAME AND TOTAL DISK SPACE FOR EACH SERVER
IF $(v_ServerLoop) = 1 THEN
LET v_ServerName = 'Dev';
LET v_ServerGigs = 338.2 * $(v_GB_to_MB);
CALL DoDir ('\\plyqlkpoc1\QlikView')
CALL DoDir ('\\plyqlkpoc1\QlikTech')
CALL DoDir ('\\plyqlkpoc1\QV Install')
ELSEIF $(v_ServerLoop) = 2 THEN
LET v_ServerName = 'Test Console';
LET v_ServerGigs = 776.2 * $(v_GB_to_MB);
CALL DoDir ('\\plyqlpappu1\PerfMon_Logs')
CALL DoDir ('\\plyqlpappu1\QlikTech')
CALL DoDir ('\\plyqlpappu1\QlikView')
CALL DoDir ('\\plyqlpappu1\QVD')
CALL DoDir ('\\plyqlpappu1\Source_Docs')
ELSEIF $(v_ServerLoop) = 3 THEN
LET v_ServerName = 'Prod Console';
LET v_ServerGigs = 1778 * $(v_GB_to_MB);
CALL DoDir ('\\plyqlpappp2\Logs')
CALL DoDir ('\\plyqlpappp2\PermMon_Logs')
CALL DoDir ('\\plyqlpappp2\QlikTech')
CALL DoDir ('\\plyqlpappp2\Source_Docs')
ELSEIF $(v_ServerLoop) = 4 THEN
LET v_ServerName = 'Test AccessPoint';
LET v_ServerGigs = 776 * $(v_GB_to_MB);
CALL DoDir ('\\plyqlkappu1\Customer_Services')
CALL DoDir ('\\plyqlkappu1\inetpub')
CALL DoDir ('\\plyqlkappu1\Install')
CALL DoDir ('\\plyqlkappu1\PerfMon_Logs')
CALL DoDir ('\\plyqlkappu1\QlikTech')
CALL DoDir ('\\plyqlkappu1\Qlikview')
CALL DoDir ('\\plyqlkappu1\SME')
ELSEIF $(v_ServerLoop) = 5 THEN
LET v_ServerName = 'Prod AccessPoint';
LET v_ServerGigs = 278 * $(v_GB_to_MB);
CALL DoDir ('\\plyqlkappp2\Inetpub')
CALL DoDir ('\\plyqlkappp2\Logs')
CALL DoDir ('\\plyqlkappp2\PerfMon_Logs')
CALL DoDir ('\\plyqlkappp2\QlikTech')
CALL DoDir ('\\plyqlkappp2\UserDocs')
ELSE
ENDIF;
// PUT INTO DASHBOARD FORMAT
Output:
LOAD
*,
IF(MATCH([File Type],'csv','txt'),'1. Text Files (csv, txt)',
IF(MATCH([File Type],'xls','xlsx','xlsb','xlsm'),'2. Excel Files',
IF(MATCH([File Type],'qvd','qvo'),'3. Qlik Data files',
IF(MATCH([File Type],'qvw'),'4. Qlik QVWs',
IF(MATCH([File Type],'No File Type'),'5. No File Type',
'6. Other File Types'))))) as [File Type Category];
LOAD
'$(v_ServerName)' as Server,
Name as File,
IF(LEFT(MID(Name,INDEX(Name, '.',-1)+1),2)='\\',
'No File Type',
MID(Name,INDEX(Name, '.',-1)+1)
) as [File Type],
MID(Name,INDEX(Name, '\',3)+1,(INDEX(Name, '\',4)+1)-(INDEX(Name, '\',3)+2)) as [Folder Level 1],
MID(Name,INDEX(Name, '\',4)+1,(INDEX(Name, '\',5)+1)-(INDEX(Name, '\',4)+2)) as [Folder Level 2],
MID(Name,INDEX(Name, '\',5)+1,(INDEX(Name, '\',6)+1)-(INDEX(Name, '\',5)+2)) as [Folder Level 3],
MID(Name,INDEX(Name, '\',6)+1,(INDEX(Name, '\',7)+1)-(INDEX(Name, '\',6)+2)) as [Folder Level 4],
Size/1000000 as [File Size MB]
RESIDENT $(v_TableName);
DROP TABLE $(v_TableName);
// CALCULATE FREE SPACE
Free_Space:
LOAD
SUM([File Size MB]) as Total_Used_Space
RESIDENT Output
WHERE Server = '$(v_ServerName)';
LET v_Used_Space = FIELDVALUE('Total_Used_Space',1);
DROP TABLE Free_Space;
CONCATENATE (Output)
LOAD
DISTINCT *;
LOAD
'$(v_ServerName)' as Server,
'Free Space' as File,
'7. Free Space' as [File Type],
'7. Free Space' as [File Type Category],
'Free Space' as [Folder Level 1],
'Free Space' as [Folder Level 2],
'Free Space' as [Folder Level 3],
'Free Space' as [Folder Level 4],
$(v_ServerGigs) - $(v_Used_Space) as [File Size MB];
// STORE EACH SERVER INFO INTO A QVD
STORE Output INTO ..\QVD\DiskUsage_$(v_ServerName).QVD(QVD);
// DROP ALL TABLES
LET vTables = NOOFTABLES();
FOR i=1 TO $(vTables)
LET vTableName = TABLENAME(0);
DROP TABLE [$(vTableName)];
NEXT;
NEXT;
-----
