Hi , am new to qlik , Am Migrating QV to QS let help clear this error
The following error occurred: Field 'NewFileDummy' not found The error occurred here: Drop Field NewFileDummy
///$tab Main
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
//**********************************************************************************************************************************************************
//*********** SET SCRIPT VARIABLES
//**********************************************************************************************************************************************************
SET ErrorMode = 1;
//Define Raw File and QVD Storage Paths
Set vFileDir = '\\bifileshare\IA_DataRepository\Underwriting Authority'; //Server Directory
Set vFilePath = '$(vFileDir)\Property';
Set vQVDPath = '..\QVD';
//Set Names for Primary Files to convert to QVD
Set vExceptions = 'CUW Exceptions Summary ';
Set vExceptionsSheet = 'CUW_Exceptions_Summary';
Set vPolicy = 'Focused Policy Summary ';
Set vPolicySheet = 'Policy_Data_Summary_Final';
Set vHitRate = 'Hit_Rate_';
Set vHitRateSheet = 'hit rate';
//**********************************************************************************************************************************************************
//*********** Call Script - Property is used as the "Test" Document so script should match what is seen on the proceding three tabs
//**********************************************************************************************************************************************************
//Calls Saved Script to Execute Update - Script is the same for Liability Package, Umbrella, and Property
$(Must_Include=$(vFileDir)\Scripts\EnvironmentScript.qvs);
EXIT SCRIPT
///$tab Script Tabs ==>
///$tab SUB_UpdateQVD
Sub UpdateQVD(xFile)
//*****************************************
//Based on file name, determine file type and define variables for incremental loading
//Assign File Type a Number for Switch Statement
Let xFileName = Subfield(SubField('$(xFile)','\',-1),'.',1);
Let xFileType = Match(Left('$(xFileName)',3),'CUW','Foc','Hit');
Let xPeriod = Right('$(xFileName)',6);
//Assign QVD name, Excel Document Prefix, Excel Sheet Name, and Period Suffix to file based on Type
Switch xFileType
Case 1 //Exceptions Summary
Set xQVD = 'ExceptionsSummary.qvd';
Set xExcel = '$(vExceptions)';
Set xSheet = '$(vExceptionsSheet)';
Case 2 //Policy Summary
Set xQVD = 'PolicySummary.qvd';
Set xExcel = '$(vPolicy)';
Set xSheet = '$(vPolicySheet)';
Case 3 //Hit Rate
Set xQVD = 'HitRate.qvd';
Set xExcel = '$(vHitRate)';
Set xSheet = '$(vHitRateSheet)';
EndSwitch
//*****************************************
//Incremental Load
If QvdCreateTime('$(vQVDPath)\$(xQVD)')
//OldData
Data:
Load * From "$(vQVDPath)\$(xQVD)" (qvd) WHERE Period <> '$(xPeriod)';
ENDIF
//NewData
Data:
Load '$(xPeriod)' as Period, *
FROM
"$(vFilePath)\$(xExcel)$(xPeriod).xlsx"
(ooxml, embedded labels, table is [$(xSheet)]);
//Store Data and Drop Table
Store * from Data into "$(vQVDPath)\$(xQVD)";
Drop Table Data;
ENDSUB
///$tab Script
//*********************************************************************************************************************
OldFileBackup:
Load * FROM [$(vFilePath)\$(vFileList)]
(txt, utf8, embedded labels, delimiter is ',', msq);
//Load Old Files to Compare to Current Files in Folder
OldFileList:
LOAD FileName,
FileTime as OldTime
Resident OldFileBackup;
//*********************************************************************************************************************
//Loop through files in file path
For each File in FileList('$(vFilePath)\*.xlsx')
//Get File Base Name
Let tFileName = SubField(SubField('$(File)','\',-1),'.',1);
Let tFilePrefix = Left('$(tFileName)',3);
//Only Match the Three Files Currently Loaded.
If Match('$(tFilePrefix)','CUW','Foc','Hit')
//Determine Period Suffix based on File Type
//Let tSuffix = Pick(Match('$(tFilePrefix)','CUW','Foc','Hit'),4,4,6);
//Load File Information
NewFileList:
Load 1 as NewFileDummy,
Right('$(tFileName)',6) as FilePeriod,
'$(tFileName)' as FileName,
Timestamp(FileTime('$(File)')) as FileTime
AutoGenerate 1;
EndIf
Next
//Store New File List
Drop Field NewFileDummy;
Store * from NewFileList into "$(vFilePath)\$(vFileList)" (txt);
//*********************************************************************************************************************
//Join on File Name and Time - Joining Old onto new to create nulls if new file found
//Any file with a different time or a new file added will have a "1" in the LoadFlag column
Compare:
Load FileName, FilePeriod, FileTime as NewTime Resident NewFileList;
Left Join
Load FileName, OldTime Resident OldFileList;
Left Join
Load FileName,
If(NOT IsNull(OldTime) AND NewTime<>OldTime,1,0) as UpdateFile,
If(IsNull(OldTime),1,0) as NewFile
Resident Compare;
//Drop Old and New File Tables - All Data Need in "Compare"
Drop Tables OldFileList, NewFileList;
//*********************************************************************************************************************
//LOAD SCENARIO SCRIPT SECTION
//SumFlagFields - Used to determine if Updating Files or Adding New Files is needed (Or Both)
LoadTest:
Load Sum(UpdateFile) as UpdateTest, Sum(NewFile) as NewTest Resident Compare;
//Set Variables for Update and New scenarios
Let vUpdateTest = Peek('UpdateTest');
Let vNewTest = Peek('NewTest');
//Determine the Load Scenario Based on vUpdateTest and vNewTest:
// 0 - No New Files or Updated Files Detected
// 1 - Only Update Files Detected
// 2 - Only New Files Detected
// 3 - Both New and Updated Files Detected
If NOT vUpdateTest AND NOT vNewTest
Let vLoadResult = Dual('Exited Without Update or New File',0);
ELSEIF vUpdateTest AND NOT vNewTest
Let vLoadResult = Dual('Only Updating Files',1);
ELSEIF vNewTest AND NOT vUpdateTest
Let vLoadResult = Dual('Only New Files',2);
ELSE
Let vLoadResult = Dual('Update and New',3);
ENDIF
//*********************************************************************************************************************
//Based on the vLoadResult, Determine What to do
//***********************NOTHING FOUND*******************
If NOT vLoadResult
//If vLoadResult is 0, exit right way (FORCE ERROR)
Set ErrorMode = 2;
Set vTesting ='Case 0';
Load * from fail.NoNewOrUpdatedFiles;
ENDIF
//***********************UPDATE FOUND*******************
//If Update Detected, Load files needing to be updated, Update Files
If vUpdateTest
//Pull File Names
Updates:
Load FileName as UpdateFiles Resident Compare Where UpdateFile;
For each File in FieldValueList('UpdateFiles')
Call UpdateQVD('$(File)')
Next
ENDIF
//***********************NEW FILE(S) FOUND*******************
//If NewFile Detected, confirm there are 3 New Files for each period found (required) and load files to be added
If vNewTest
//Test Files per Period
//Count Files by Type
NewPeriodCount:
Load FilePeriod, Count(FileName) as PeriodFiles Resident Compare Where NewFile Group By FilePeriod;
//Make sure each period that has a new file has *** files
NewPeriodTest:
Load If(Max(PeriodFiles)=3 AND Min(PeriodFiles)=3,Dual('PeriodTestSuccess',1),Dual('PeriodTestFail',0)) as NewPeriodTest Resident NewPeriodCount;
//Store Test in Variable
Let vNewPeriodTest = peek('NewPeriodTest');
//If Test Successful, Load New Files Else Force Failure
If vNewPeriodTest
NewFiles:
Load FileName as NewFiles Resident Compare Where NewFile;
ELSE
Set ErrorMode = 2;
Store * from OldFileBackup into "$(vFilePath)\$(vFileList)" (txt);
Load * from fail.NewPeriodMismatch;
ENDIF
//Update QVDs
For each File in FieldValueList('NewFiles')
Call UpdateQVD('$(File)')
Next
ENDIF
///$tab CleanUp
//Drop All Tables
LET vTables = NoOfTables();
FOR i=0 to $(vTables)-1
LET vTableName = TableName(0);
DROP Table [$(vTableName)];
NEXT;
EXIT SCRIPT
///$tab <== Script Tabs
///$tab Initial File List
//Loop through files in file path
For each File in FileList('$(vFilePath)\*.xlsx')
//Get File Base Name
Let tFileName = SubField(SubField('$(File)','\',-1),'.',1);
Let tFilePrefix = Left('$(tFileName)',3);
//Only Match the Three Files Currently Loaded.
If Match('$(tFilePrefix)','CUW','Foc','Hit')
//Determine Period Suffix based on File Type
//Let tSuffix = Pick(Match('$(tFilePrefix)','CUW','Foc','Hit'),4,4,6);
//Load File Information
NewFileList:
Load Right('$(tFileName)',6) as FilePeriod,
'$(tFileName)' as FileName,
FileTime('$(File)') as FileTime
AutoGenerate 1;
EndIf
Next
//Store New File List
Store * from NewFileList into "$(vFilePath)\$(vFileList)" (txt);
EXIT SCRIPT
