-
Collabtive – project management software
Posted on May 7th, 2009 No commentsI think i finally found management software that i need. I try i a lot of application but something always wasn’t all right. Collabtive is very friendly and easy to work with.
The best things about it are that development is continues very hard and it’s free and Open Source.
Collabtive Key features
- Projects, Milestones, Tasks
- Basecamp import
- Timetracking, Reporting
- php
- MySql
I play today with Collabtive database and make some basic QlikView report for tasks and will include Collabtive blog posts and comments.Collabtive can be downloaded from here.
-
QlikView – Export chart data on specific date to xls file
Posted on September 18th, 2008 2 commentsHello,
today is very useful day :-D
In this post i’ll show you how to expand and export (in excel format) pivot table on specific date for each value in list box using sub macros.So the situation is that you want to generate excel files for each country (for example) on specific date. Instead to do this manual you can achieve this using macros (VBScript) in QlikView. Below is the code:
sub Exports// Add dates on which files will be generated in arrayDim dateList(3)
dateList(0) = #01/08/2008#
dateList(1) = #15/08/2008#
dateList(2) = #18/09/2008#// For each value in array chech that match todayFor each reportDate in dateList
If reportDate = Date Then// Exported table will be CH01set Table = ActiveDocument.GetSheetObject("CH01")// Clear all selections that was madeActiveDocument.ClearAll false// Get all values in Country field
set val=ActiveDocument.Fields("Country").GetPossibleValues// For each Country do
for i=0 to val.Count-1// Select each country
ActiveDocument.Fields("Country").Select val.Item(i).Text
// Expand third cell in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 1,0,false// Expand second cell in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 1,0,false// Expand Expand all cells in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 0,0,true// Get data in CH01
set obj = ActiveDocument.GetSheetObject("CH01")
// Save data from table in file.
//Example for file name: Projects_Bulgaria.xls
obj.ExportBiff "C:\Projects_"&val.Item(i).Text&".xls"
next
End If
NextAfter creating this macros you may put it in OnPostReload (Settings –> Document Properies –> Macros) event in the document so after each reload this macros will be trigerred.
-
QlikView – extract data from comma separated cell
Posted on September 18th, 2008 3 commentsHello all who read this :-)
Today i have a collision with little odd situation. I have an excel file with 2 columns – ID and Users. The problem was that in the Users cells the data was comaseparated like this:
ID Users 1 User1,User2,User3 2 User4,User5,User6 3 User7,User8,User9 So the situation i want is to have table with only one use per row. Something like this:
ID Users 1 User1 1 User2 1 User3 2 User3 2 User4 2 User5 Below is a QlikView script that achieve this:
// // ++++++++++ Load data from excel ++++++++++for a=0 to 20 // // Presume that there will no be more than 20 users listed.// // For each a from 0 to 20 do load from excel where substring count , is equeal to aExcelData:LOAD
ID,
Users
$(a) AS a
FROM [C:\Users.xls] (biff, embedded labels, table is [Users$])
WHERE substringcount(Users, ',') = $(a);
// // For each a extract all listed users. The result is that for each listed customer for request
// // there is a single record
for b=0 to $(a)
UnQualify *;
TransforemdExcelData:
Load
ID,
if( substringcount(Users, ',') = 0, Users,
mid(Users&',', index(Users&',', ',',$(b))+1, index(Users&',', ',',$(b)+1) - index(Users&',', ',',$(b))-1)
) AS User,
a
Resident ExcelData;
next
next
// Finaly TransforemdExcelData may be loaded again and exclude nullable records
// // ++++++++++ END loading data from excel++++++++++
// // Load all data in new table excluding empty record
Qualify a;
Users:
Load
ID,
User,
a
Resident TransforemdExcelData
Where User<> '';
