QlikView ODBC Lotus Notes

Thanks to QV - Community Post for details : http://community.qlik.com/thread/12541

Very useful for LOTUS Note Connection

To use Notes data through ODBC, you must have:

• Notes SQL, the Lotus Notes ODBC driver
• An ODBC Driver Manager version 3.5 or later
• One of the following:
  • Microsoft Windows 2000, or XP
  • Microsoft Windows 2003 Server Standard Edition or Enterprise Edition
  • Microsoft Windows Vista
  • Microsoft Windows 2008
  • Microsoft Windows 7
• One of the following:
  • Lotus Notes Client release 6.0 or later
  • Lotus Domino release 6.0 or later
  • Lotus Notes Designer release 6.0 or later
  • Lotus Domino Off-Line Services release 1.01 or later

OK, and then few steps: (this works in windows 7 x64)

1) Install NotesSQL - driver  to C:\NotesSQL

2) add your Lotus Notes directory to the %PATH% Variable

3) Reboot

4) run c:\windows\syswow64\odbccad.exe

5) You should now find the NotesSql ODBC driver to create the ODBC connection normally

QlikView - How to delete category from Access Point !

Customer Requirement:

QV Server Admin / Customer usually creates Document Categorization for Access Point Application grouping.

This is done using under 

• "http://localhost:4780/QMC/UserDocuments.htm#" - > "User Documents" - > "Document Information"

Issues:

•  Also during UAT phase Multiple Categorization were created.
• But as we move to live environment and everyone would like to delete category from the Access Point.
• At this moment - there is no Wizard or QMC settings to do the same.

Solution:

We need to manually delete the categorization under  

•  C:\ProgramData\QlikTech\ManagementService\QVPR\Category.xml 
• Delete the line that corresponds to the category you are looking for.

Thanks QV Support & Community Posts for helping on this issue.

Qlikview Scan folder for Files & Reload Data from Scanned Files

Most of the business implementation - works with File Dump into folders - By Specific Naming Conventions - By Month.
Below script helps to Scan & Reload specific Source files into QlikView.

/////////////////////////////////////////////////////////////////////////////
///$(Loop ForeCast)
/*
Directories that should be scanned for qvw files.
One directory per line. Paths may be local or UNC ( \\server\shareName\Dir\file ) paths.
*/
DirectoriesToScan:
LOAD * INLINE [
Dirspec
C:\QlikView\DataSources\Excel\Forecast
]
;
/////////////////////////////////////////////////////////////////////////////
/*
Subroutine to scan directories and load stats about qvw files.
*/
SUB doDir (dir)
FOR EACH file in filelist('$(dir)' &  '\*.xlsx') // Excel 2007 Formats only :: xlsx

Files:
LOAD *
,$(#SCRIPT_START) - FileTime as FileAge
 where len(FileName)='21'; // Valid File Name "2014 07 Forecast.xlsx"
LOAD '$(file)' as FilePath
,FileSize('$(file)') as FileSize
,FileTime('$(file)') as FileTime
,subfield('$(file)', '.', -1) as FileExtension
,subfield('$(file)', '\', -1) as FileName
,subfield(subfield('$(file)', '.', 1),'\',-1) as FileBaseName
AUTOGENERATE 1
;
NEXT
FOR EACH subdir in dirlist( '$(dir)' & '\*.xlsx' )
  CALL doDir('$(subdir)')
NEXT
END SUB

SUB doRoot (root)
FOR EACH subdir in dirlist( '$(root)' )
 CALL doDir('$(subdir)')
NEXT
END SUB

/////////////////////////////////////////////////////////////////////////////
/*
Call the scanning sub for each directory specified in the Configuration tab.
*/
FOR i = 0 to FieldValueCount('Dirspec')
Call doRoot(FieldValue('Dirspec',$(i)));
NEXT i
DROP Table DirectoriesToScan;
/////////////////////////////////////////////////////////////////////////////

FOR i = 0 to FieldValueCount('FilePath')
if(len(FieldValue('FilePath',$(i)))>0) then
Call Forecast_Exctract(FieldValue('FilePath',$(i)));
ENDIF;
NEXT i

/////////////////////////////////////////////////////////////////////////////
///$(QVD_Extractor)
//Remeber to place Sub - before Call functions
sub Forecast_Exctract(vFile_Path)

LET vFileBaseName=subfield(subfield('$(vFile_Path)', '.', 1),'\',-1);
Let vQVDName='C:\QlikView\DataSources\QVD\Forecast\$(vFileBaseName).qvd';

TRACE '$(vFile_Path)';
Forecast_RAW:
LOAD FileBaseName() as Forecast_Source,
*
FROM
'$(vFile_Path)'
//[..\DataSources\Excel\Forecast\2014 07 Forecast.xlsx]
(ooxml, no labels, header is 3 lines, table is [Forecast], filters(
Transpose(),
Replace(3, top, StrCnd(null)),
Transpose()
));

STORE Forecast_RAW into $(vQVDName)(qvd);
DROP Table Forecast_RAW;
end sub
/////////////////////////////////////////////////////////////////////////////

--

Thanks to QlikView Community posts & Robwunderlich - File Monitoring application

//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// 
//////////////////////////////////////// MINI VERSION //////////////////////////////////////// 

1. Sub ScanFolder(Root)  
2.           for each FileExtension in 'xlsx'  
3.               for each FoundFile in filelist( Root & '\*.' & FileExtension)  
4.                  FileList:  
5.                  Load  Subfield('$(FoundFile)','\',5) as Filename,  
6.                        Subfield(Subfield('$(FoundFile)','\',5),'_',2) as Market,  
7.                        Subfield(Subfield('$(FoundFile)','\',6),'_',3) as Unit  
8.                               Autogenerate(1);  
9.                     next FoundFile  
10.           next FileExtension  
11.           for each SubDirectory in dirlist( Root & '\*' )  
12.                     call ScanFolder(SubDirectory)  
13.           next SubDirectory  
14. End Sub  
15.   
16. Call ScanFolder('C:\Users\Tamil\Desktop\Test') ;  

//////////////////////////////////////// //////////////////////////////////////// ////////////////////////////////////////