Qlikview Variables

Posted on August 29, 2008 by mellerbeck

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/

WickBrick

SET versus LET

SET Var1 = 2 * 3

Takes what ever is on the right and places it in the variable as literal text. So Var1 will = 2 * 3

If you use the LET (LET Var2 = 2 * 3)

It will evaluate and place the result into the variable. Var2 will = 6

How does QlikView handle these variables?

In the UI you can refer to a variable in two ways either by Var1 which will return 2 space asterisk space three.

If you refer to it using $(Var1) (wrapping in dollar sign open param close param) this is read as the evaluation of Var1 which will evaluate it at run time.

Var2 has already been evaluated so if you refer to it as Var2 or $(Var2) it still is going return a = 6

So the big difference between LET and SET is that the LET will be evaluated during the script, if you use the SET you can have have the variable evaluated during the UI.

One crazy way of using a variable is if in your load script if you had (the doc being named ChangeTable.qvw)

An ODBC connect string and then

SELECT * FROM $(MyTable);

Then you can reload from the command line

c:\Program Files\QlikView\qv.exe ChangeTable.qvw /l /vMyTable=Products

The /l means reload it and keep it open, and the /v means pass a variable to the QlikView

Another way of using Variables, is that you can have Expressions stored in either a xls document or a database.

Filed under: Applied IT, Qlikview | Tagged: , , , , | 1 Comment »

I actually got ZFC Zero Foot Print Client to work with Qlikview 8.20

Posted on August 28, 2008 by mellerbeck

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/

WickBrick

So I was trying hard to get the ZFC to work and I kept on getting

“Failed to open document, file not found.”

grrr it was absolutely driving me crazy!

So here is the deal, When you go Tools, Objects Client Page Generator

You would think that it would default to the correct Document URL.

No, it does not. Not even close. And it does not even check it if it’s valid…. lame

Dear QlikView people, please add a browse button for the Document URL of the Objects Client Page Generator! I know you can do it! If I enter in my web browser qvp://myservername it gives me a great document browser!!!!

Anyways, so the first key is that you need the Document URL to actually point to your document. Duh. This is where it got interesting for me though because if I use the above qvp:// browser it does not give me the path. So how do I find out the path?

Well open the qlikview that you are wanting to ZFC (though the web) either browse there by qvp:// or use http://myserver/qlikview and picking it from the list

Now hit ctrl + shift + q which brings up the Document Support Information which lets you know your qvp:// path. There was that so hard. In my case we have a QVW which is named Production_Summary.qvw in the Inventory folder so I was assuming the path would be qvp://server/Inventory/Production_Summary.qvw

(Notice the Underscore between Production and Summary) and in fact when I run the Objects Client Page Generator it even defaults by putting in the underscore!!!!!!!)

But the real qvp:// is qvp://server/Inventory/Production Summary.qvw without the underscore.

I’m not sure if this is a bug or intentional but it sure is annoying!

So once you find the actual real path. Things get a little easier. The next error you will get is unable to access file. This is a permission problem. I am not sure about qlikview security at all, how session licenses versus User CALs work. Someone should explain it for me 🙂

I read on the Qlikview forums that you needed to disable anonymous access to get the ZFC to work so this is what I did. I got it to work using c:\inetpub\wwwroot\test as my target folder.

Then opening up, IIS manager, creating a new virtual directory pointing to c:\inetpub\wwwroot\test (When creating the virtual dir, I also allowed script execution don’t know if this matters)

Then permissions, I right clicked on my newly created virtual dir, properties, directory security tab, edit, and then unchecked Enable anonymous access. Then I did this on the one the QVAJAXZfc folder as well!!!

Finally I navigated to my http://server/test directory and saw my ZFC for the first time! Let me know if it works for you or if you know anything about this!

Filed under: Applied IT, Qlikview | Tagged: , , | 2 Comments »

Some quick tips for helping remote users

Posted on August 28, 2008 by mellerbeck

So the first question we always ask when a user has a problem is, what is your computer name.

You can always go the right click on the mycomputer icon, then properties, the computer name tab, and then read me the name after Full computer name.

The cheater way is to tell them hit start, run, and then type %computername%

It will give them an error that contains their computer name, nifty huh!

***

Another thing I sometimes have people do is flush their dns cache.

Usually the command is ipconfig /flushdns but I can never remember whether it is a forward or a backslash and sometimes the user does not know the difference anyways. So the solution

ipconfig -flushdnss , everyone knows where the dash is! Just eliminate that hassle

Filed under: Applied IT | Tagged: | 1 Comment »

Parameterized Sql statement vb.net

Posted on August 28, 2008 by mellerbeck

Very basic but, never do this

Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand("select * from Customers 
where city = '" + inputCity + "'", connection)

Instead

Private Sub UpdateDemographics(ByVal customerID As Integer, _
    ByVal demoXml As String, _
    ByVal connectionString As String)

    ' Update the demographics for a store, which is stored 
    ' in an xml column.
    Dim commandText As String = _
     "UPDATE Sales.Store SET Demographics = @demographics " _
     & "WHERE CustomerID = @ID;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        ' Add CustomerID parameter for WHERE clause.
        command.Parameters.Add("@ID", SqlDbType.Int)
        command.Parameters("@ID").Value = customerID

        ' Use AddWithValue to assign Demographics.
        ' SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml)

        Try
            connection.Open()
            Dim rowsAffected As Integer = command.ExecuteNonQuery()
            Console.WriteLine("RowsAffected: {0}", rowsAffected)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using
End Sub

From http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Filed under: Uncategorized | Leave a comment »

QlikView InLine Data

Posted on August 25, 2008 by mellerbeck

So if you ever need to embed some data, like if you wanted to define a week to quarter relationship, the Inline Data Wizard is the way to go. Click the Inline Wizard button and then type in your data.

This will make you something that looks like this

LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q1
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];

Note! Empty cells in Load inline statements represent an ’empty string’ rather than null.

Filed under: Uncategorized | Tagged: , | Leave a comment »

Qlikview, Circular References

Posted on August 22, 2008 by mellerbeck

Another common problem.

Occurs when associations between three or more tables go around in a circle. If it happens QlikView will give you a warning and find the table with largest amount of records and make it loosely coupled.

Filed under: Uncategorized | Leave a comment »

Qlikview Synthetic Keys

Posted on August 22, 2008 by mellerbeck

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/

WickBrick

Synthetic keys occur when two or more tables have two or more fields in common.

Like if you had an ORDER_DETAILS table that had an Order ID and a ProductID and a SHIPMENTS Table that had an OrderID and a ProductID. This would cause a synthetic key.

Qlikview then creates a synthetic table. Synthetic keys are not in and of themselves errors, but they have serious performance implications. If you have a small dataset then you don’t need to worry. These take up a lot of memory because QlikView tries to store every combination of the values stored in the fields. Synthetic keys can cause inconsistency in the data structure.

If you have an app with multiple multiple synthetic keys, 7 or a dozen and some might have 4 or 5 fields, and bringing in millions of records your QlikView might not run at all. The symptom that you will see is the script editor will run all the way through, and you will get a wait cursor, and then your harddrive will start churning as qlikview tries to generate all the synthetic keys.

How to resolve, one way if you don’t have very many fields is to rename every field that you don’t want associated. Or you could comment out references in the Load statement from one of the tables.

Another way is by concatenating the two common fields together in both tables and use that to join on.

For example

Load
[Order ID] & ‘|’ & ProductID as ShipmentKey,
[Order ID],
ProductID,
[Unit Price],
Quantitay,
Discount
FROM SynKey.xls (biff, embedded labels, table is [ORDER_DETAILS]);

Directory2:
[Order ID] & ‘|’ & ProductID as ShipmentKey,
//[Order ID],
//ProductID,
[Unit Price],
Quantitay,
Discount
FROM SynKey2.xls (biff, embedded labels, table is [ORDER_DETAILS]);

A pipe ‘|’ is used to concat because it is not found anywhere in the dataset. You would then remove the duplicate fields from one table (In this case comment out the OrderID, and ProductID.

Or, if you have a lot of fields use the QUALIFY statement.

The field name will be renamed when found in a table. It will be in the form tablename.fieldname

Examples

Qualify B;
Load A,B from x.csv;
Load A,B from y.csv;
The two tables x.csv and y.csv will be joined only on A. Three fields will be
found: A, x.B, y.B.

Qualify Comment;
turns qualification on for all fields named Comment.

Qualify *;
turns qualification on for all field names.

Qualify “*ID”;
turns qualification on for all field names ending with ID.

Qualify “NR*”,A;
turns qualification on for A and all field names starting with NR.

Qualify F??;
turns qualification on for three character field names starting with F.

To link tables you need to Qualify one field. To do so use the UNQUALIFY statement for that field.

When you are done you need to use an UNQUALIFY *; to that the QUALIFY statement does not apply to other tables.

Another way is using the AutoNumber (if you had multiple, multiple fields that you needed to concat)

When you pass a unique string to the AutoNumber function it will give you back a unique integer. This creates a much more efficient key.

There is a problem!!!! If you are storing data in QVD’s external to a Qlikview Document, AUTONUMBER WILL NOT RETAIN UNIQUENESS ACROSS THE QVD’S.

In this case, new to version 8, is the Hash128 function.

This is very similar to the Autonumber function, it is not as small as the Autonumber but it WILL retain uniqueness across loading of QVD’s

Filed under: Qlikview | Tagged: , | 1 Comment »

Next Page »