Wednesday, November 19, 2014

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


Sunday, November 16, 2014

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.



Monday, September 1, 2014

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') ;  
//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// 

Thursday, December 26, 2013

QlikView - Replacing NULL with Dummy Values

Below QlikView Edit Script will help to Replace NULL values with Dumy Values ::

MissingMap:
MAPPING LOAD
null(), 'Others!' AutoGenerate 1;

MAP [Business Line] USING MissingMap;
MAP Product_Family_Desc USING MissingMap;
MAP Cell USING MissingMap;
MAP Group USING MissingMap;
MAP Line USING MissingMap;

Material_Order_Master2:
NoConcatenate   // This is important! We want a new table!
LOAD * RESIDENT Material_Order_Master;
DROP TABLE Material_Order_Master;  // Drop the original table

Tuesday, December 10, 2013

QV - Firewall - Exception & Port Details

Below Cmd line codes helps to create FireWall exception for the QlikView Server.

netsh advfirewall firewall add rule name="QV Accespoint HTTP" dir=in profile=any action=allow protocol=TCP localport=80
netsh advfirewall firewall add rule name="QV Accespoint HTTPS" dir=in profile=any action=allow protocol=TCP localport=443
netsh advfirewall firewall add rule name="QV Access for Comand Center" dir=in profile=any action=allow protocol=TCP localport=4710
netsh advfirewall firewall add rule name="QV Access for Directory Service Connector" dir=in profile=any action=allow protocol=TCP localport=4730
netsh advfirewall firewall add rule name="QV Access for Distribution Service" dir=in profile=any action=allow protocol=TCP localport=4720
netsh advfirewall firewall add rule name="QV Access for QMC / QEMC" dir=in profile=any action=allow protocol=TCP localport=4780
netsh advfirewall firewall add rule name="QV Access for QV client / QlikOCX" dir=in profile=any action=allow protocol=TCP localport=4747
netsh advfirewall firewall add rule name="QV Access for WebServer" dir=in profile=any action=allow protocol=TCP localport=4750
netsh advfirewall firewall add rule name="QV Access Server Tunnel" dir=in profile=any action=allow protocol=TCP localport=4774
netsh advfirewall firewall add rule name="QV LDAP port" dir=in profile=any action=allow protocol=TCP localport=389

netsh advfirewall firewall add rule name="QV LDAP port over SSL" dir=in profile=any action=allow protocol=TCP localport=636

Tuesday, December 3, 2013

QV - Master Calendar Scripts !!!

Master Calendar Scripts All Time :)

Quarter_Map:
Mapping
LOAD * INLINE [
    Month, Quarter
    Jan, Q4
    Feb, Q4
    Mar, Q4
    Apr, Q1
    May, Q1
    Jun, Q1
    Jul, Q2
    Aug, Q2
    Sep, Q2
    Oct, Q3
    Nov, Q3
    Dec, Q3
]
;

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

SET vDateMin = Num(date('01/01/1950','DD/MM/YYYY'));
SET vDateMax = Num(date('31/12/2050','DD/MM/YYYY'));
LET vDateToday = Num(Today()); 
 


//LET vDateMin = Num(Peek('Open_DateTime', 0, 'Interval_Table'));
//LET vDateMax = Num(Peek('Close_DateTime', -1, 'Interval_Table'));
//LET vDateToday = Num(Today()); 

TempCalendar1: 
LOAD
$(vDateMin) + RowNo() - 1 AS Date_Key, 
Date($(vDateMin) + RowNo() - 1) AS Date 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Master_Calendar:
load Date_Key
       ,
Date
       ,
Date as CalendarDate
       ,
Day(Date) as CalendarDay
       ,
Week(Date) as CalendarWeek
       ,
WeekName(Date) as CalendarWeekName
       ,
month(Date) as CalendarMonth
       ,
MonthName(Date) as CalendarMonthName
       ,
MonthName(Date) as CalendarPeriod
       ,
Quartername(Date) as CalendarQuarterName
       ,
ApplyMap('Quarter_Map',num#(month(Date)))as CalendarQuarter 
       ,
year(Date) as CalendarYear
       ,
WeekDay(Date) as CalendarWeekDay
       ,
text(weekday(Date)) as Week_Day1

       ,WeekDay(Date) as CalWeekDay
       ,Week(Date) as CalWeek
       ,week(Date+3) as CalRWeek    
       ,WeekName(Date) as CalWeekName
       ,WeekName(Date,1,4) as CalWeekRName    //4=Fri     // Friday - Thursday Business Week
       ,MakeWeekDate(Year(Date),Week(Date)) as CalWkSD       
       ,num(MakeWeekDate(Year(Date),Week(Date))) as CalWkSDNum            
       ,MakeWeekDate(Year(Date+3),Week(Date+3),-3) as CalWkRSD      
       ,num(MakeWeekDate(Year(Date+3),Week(Date+3),-3)) as CalWkRSDNum  


Resident TempCalendar1;
DROP Table TempCalendar1;


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Thx for the QlikCommunity Post : by Jagan Mohan 
https://community.qlik.com/docs/DOC-9146
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
"Master Calendar with every 30 Minutes"

LET vMinDate = Num(MakeDate(2014,1,1));  // Calendar Start Date
LET vMaxDate = Num(Today()); // Calendar End Date
LET vDays = vMaxDate - vMinDate + 2;  // Calculating number of days between Start & End Dates

Calendar:
LOAD Date(Floor(TimeStamp)) AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute;
LOAD
    Timestamp($(vMinDate) + (RecNo() - 1)/48) as TimeStamp

AUTOGENERATE 48 * $(vDays);

Wednesday, October 2, 2013

It is possible to recover the Document CAL License?

Scenario:
 User deleted the QlikView Document, for which the licenses were allocated. and also "Actually we forgot the Document Name"

Solution:
1)Stop the QlikView Server service.
2)Add PgoAsXmlAlso=1 under [Settings 7] in the Settings.ini file located in %ProgramData%\QlikTech\QlikViewServer (default location)
3)Restart the QlikView Server service
4)Navigate to Root Directory and Open the CalData.pgo.xml file to determine to which document the
CALs were assigned
5) Create a new blank QVW file named the same as document listed in CalData.pgo.xml & save it in
the Root directory
6) In Management Console, navigate to Documents > User Documents and locate QWV created in the
previous step
7) Click Document CALs tab and set Number of CALs allocated to this Document to zero and click
Apply


--
Thanks to QlikView Community posts