QlikMan

DAMN YOU ZEROS AND NULLS!!!

2010-11-26T08:59:00.000-08:00

Damn, lots of time wasted with sheets and checking!!! The Excel seems to keep fighting the almighty Qlik.

Basically I am creating a table that will show the survey form in a tabular way. I have some columns that have 0 values and other with null values.

The Qlikview has some options to treat zeros and nulls. Below is a little explanation

WHAT:
How to show the columns in a pivot table with the real values if they are zero or null. Check the question 4 (column 5) and CODAVAL 173624 and 173626 (rows 7 and 8).

HOW:

The default configuration comes with a set of flags that you can not set again after you have made the changes.

The result is:

Note that the question 4 is gone and that I have only one survey form shown (CODAVAL)

Setting the configuration below

Makes the null becomes 0.

Set hide zeros

It will make the 0s disappear:

Last but not least, the "correct" form in this case would be:

Making the zeros shown as zeros and the null columns as null.

Now if you export the table to an Excel, and make some calculus over it, it will show the same values.

Avg, sums, etc would work as a charm on an Excel sheet.

Cheers!!

Bye bye NAVIGATION TABS

2010-10-11T08:50:00.000-07:00

After a little vacation, let´s get to work.

I don´t know why, but I don´t like using the navigation tab buttons on my reports. Probably it is because I have to set lots of properties and create the same objects (filters, logo, report name, etc) for each of the tabs.

A couple of months ago I started using object´s conditional show to create a tab that would have all my objects. When pressing a "Dashboard" button, the dashboard objects would appear. When pressing a "Regional" button, only the regional objects would appear.

The biggest problem with this approach is that I needed to set individually the condition that would make the objects appear or not.

To make my job easier, I have come with the macro below so that I can write my condition, choose the objects I want and then set for all of them the same condition making the development and maintenance easier. It is almost a group condition.

WHAT
I have took the macro below and changed it a little to allow setting a "conditional show" for multiple objects.

HOW
You cannot use a button to call the macro. If you do, when pressing the buttons the objects you have selected to apply the macro will be deselected and the macro will be applied over the button. We need to make a little workaround when using the macro.

1. first create 2 vars: vLayout and vShowCondText.

2. copy the code to the Macro Module:

sub setShowCondition
' vars
set doc = ActiveDocument

' grab the text to be used as the condition
condText = ActiveDocument.Variables("vShowCondText").GetContent.String
' msgBox(condtext)

' traverse the selected objects setting the new condition text
set s=doc.ActiveSheet
objs=s.GetActiveSheetObjects
for i=lbound(objs) to ubound(objs)
' get the object's name and property
set objInt = objs(i)
objID = replace(objInt.GetObjectId,"Document\","")
set obj = doc.GetSheetObject(objID)
objProp = obj.GetProperties

' as each object has diferent places for the frame object, treat them differently
select Case objs(i).GetObjectType
     Case 1,2,3,4,6,7,8,9,17,18,19,34,35 'LB,MB,SB,TB,TX,CS,IB,LA
    objProp.Layout.Frame.Show.Always = false
    objProp.Layout.Frame.Show.Expression.v = condText
     Case 5,31 'BU,SO
    objProp.Frame.Show.Always = false
    objProp.Frame.Show.Expression.v = condText

     Case 10,11,12,13,14,15,16,20,21,22,27,28 'CH's
     objProp.GraphLayout.Frame.Show.Always = false
    objProp.GraphLayout.Frame.Show.Expression.v = condText
    Case Else
     msgbox("ObjectID: " &objID & " with objectType: " & objs(i).GetObjectType &" couldn't be found, trying LB settings")
     objProp.Layout.Frame.Show.Always = false
      objProp.Layout.Frame.Show.Expression.v = condText
     end select
obj.SetProperties objProp
  next
end sub

USING THE MACRO

1. Create the button.

2. Configure it to set the value to the vLayout variable.

3. Write the condition you want. I have created an input object that will set automatically the value to the var vShowCondText.

4. Select the objects you want to set the condition.

5. Now, the workaround part. Open the Module Screen (CTRL+M). Select the function setShowCondition on the left side.

6. Below the function list, click the TEST button . It will execute the macro chosen before.

That´s it. When pressing the new button, the objects will appear. You may want to create a logic to set/unset the vLayout variable so that the same button enables/disables the objects, or create another button that will set another value to vLayout, making the objects with the value PRINCIPAL to disappear appearing the new ones.

Hope it helps. Please comment.

You have the right to remain still!!

2010-09-15T17:34:00.001-07:00

All the credits go for Guerrila BI. Nice macro he did. Check the original post at http://guerrillabi.com/node/26

Before publishing a Report it is good to make all the objects static. This is specially true with the version 9 that the user is allowed to create his own objects and move the original ones.

I was making my little precious this morning when I bumped into the post above. The first thing I though was F#$. I had spent a couple of ours in the making to discover that the work was already done.

Anyway, below goes the code with a little adaptation:

What

Create a set of button that will enable/disable the move operation for all objects. Perfect for report design then publish.

How

At the edit module (CTRL+M) paste the code below. It will basically traverse all sheets and all objects to enable/disable move. The commented code is the original code that I did not use. Maybe you will.

' Enable the move resize for all objects on the report.
sub EnableMove

' do the magic
setMove TRUE,TRUE
end sub

' Disable the move resize for all objects on the report.
sub DisableMove

' do the magic
setMove FALSE,FALSE
end sub

' Set the move properties to all objects
'msVal - Move/Size setting for non-chart objects
'chVal - Copy/Clone setting for non-chart objects
'amVal - Auto Minimize setting for chart objects
'ccVal - Move/Size setting for chart objects
function setMove (msVal, ccVal)
set doc = ActiveDocument

' for all the sheets set the property chosen
for j = 0 to ActiveDocument.NoOfSheets - 1
set s=ActiveDocument.GetSheet(j)
objs=s.GetSheetObjects

' for all objects
for i=lbound(objs) to ubound(objs)

' get the object's name and property
set objInt = objs(i)
objID = replace(objInt.GetObjectId,"Document\","")
'msgbox("obj ID " & objID &" has the objType " & objs(i).GetObjectType)
set obj = doc.GetSheetObject(objID)
objProp = obj.GetProperties

' as each object has diferent places for the frame object, treat them differently
select Case objs(i).GetObjectType
Case 1,2,3,4,6,7,8,9,17,18,19,34,35 'LB,MB,SB,TB,TX,CS,IB,LA
objProp.Layout.Frame.AllowMoveSize = msVal
'objProp.Layout.Frame.AllowCopyClone = ccVal
Case 5,31 'BU,SO
objProp.Frame.AllowMoveSize = msVal
Case 10,11,12,13,14,15,16,20,21,22,27,28 'CH's
objProp.GraphLayout.Frame.AllowMoveSize = msVal
'objProp.GraphLayout.Frame.AllowCopyClone = ccVal
'objProp.GraphLayout.Frame.AutoMinimize = amVal
Case Else
msgbox("ObjectID: " &objID & " with objectType: " & objs(i).GetObjectType &" couldn't be found, trying LB settings")
objProp.Layout.Frame.AllowMoveSize = msVal
'objProp.Layout.Frame.AllowCopyClone = ccVal
end select
obj.SetProperties objProp
next
next
end function

Now create the buttons and call the subs EnableMove and DisableMove. Good thing to let them hidden before publishing.

And VIVA LA REVOLUCIÓN!!!

Finding differences with QlikView.

2010-09-14T16:19:00.000-07:00

After various months checking synchronization data I have done a report that will tell me which information is present in one side and not in the other. After creating that, I needed to check why the hell the info didn't went to the other system. After all the analysis I had a list with the items that had problems.

Good. Now what? Export to excel and create the query. These days are gone for me!!

What:
Create the search query based on a list box selection of the report.

How:

I have made an inline load with all the queries I use to check (all the tables as well) info separated by System and subsystem.

LOAD * INLINE [
   SYSTEM, SUBSYSTEM, QUERY
   SENDER, INVOICES, "select * from INVOICES a, INVOICES_BODY b where a.invoice_num = b.a.invoice_num and a.invoice_num in ( "
   RECEIVER, INVOICES, "select * from usr_inv.inv_data a, usr_data.invoices b where a.code = b.code and b.code in ( "
];

Loaded the invoice nr for both system in the same table, having a column called system with the system name RECEIVER and SENDER;
Created two tables, one only with RECEIVER and other with SENDER data using set analisys on expression ( count ( {<SYSTEM= {'SENDER'}>} distinct INV_NR ) ) and the SUBSYTEM as dimension;
Created one calculated list (Nr of Systems) with the number of system that an Invoice is present ( =aggr(count(distinct SUBSYSTEM) , INV_NR ) ). As we have 2 systems here, it should show 1 for invoices only on one system and 2 for both systems;
Created a filter for the SUBSYSTEM;
One textbox that will convert the list to string and merge with the query ( =Only(QUERY) & Concat(CODREALIZACAO,',') &' )' ). This will only be shown if I have selected a Subsystem and Nr of Systems.

When clicking the list of number of system, it will show me the amount I have in one side and not on another. Clicking on System, will show my List Box and will show me the query I need to executed over a PL/SQL Program.

That´s it. The versatility of Qlikview, it´s clicking options and the possibility of loading from multiple sources.

Custom Buttons

2010-08-25T06:21:00.000-07:00

Custom Buttons

Not a great tip, but it could make things a little more stylish.

What:

Qlikview has a functionality to use custom button design. It is basically a 3 state image that will be the active state, pressed state and the inactive state.

3 state image

How:

Crate the image with the 3 states side by side with the order stated below. Add a button to the sheet, click with the right mouse button and then properties.

Main sheet, Background select image and below it Combined Image. Select the image and voilà.

Regular button versus custom button.

Blinking Lights on QlikView

2010-08-17T10:04:00.000-07:00

Well,

this is the first in this new blog.

Sometimes managers ask for a little more than a static BI. They want a PPT full of animations but with the functionalities of a PPT. I´ve tried a couple of animation tests, but no real success as Qlikview updates the screen only after the macro is run.

This will not change your life, but may help you to improve your report a little:

- when choosing a color for a gauge choose to be a calculated color.
- check the RGB code for the color you want and use the function =RGB(220*vBlink,48*vBlink,52*vBlink) replacing the numbers by the RGB code you have.
- use the edit variable screen (CTRL+ALT+V) and create a variable called vBlink and set it to be =Mod(Second(now()),2)
- there you go. Lights Blinking

Explanation:
- Mod function will return the rest of the division operation by the second function by 2 (in this case)
- By using the Second function, the var is constantly updated and the rest (1 or 0) is passed to the variable
- The RGB function will be (0,0,0) - black or (220,48,52) - the color I wanted.

And that´s the trick