topic Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions in QlikView

Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Thu, 14 Mar 2013 16:13:06 GMT

Recently I got a assignment from the owner of my company to make our standard reports include what we call UDFs (User-Defined Fields) so that each client (which resides in a different db) can have any or all of their UDFs included in a existing report without it being different from client to client or db to db. Also each client has a variable number of UDFs as they are unlimited.

The tables involved are:

CustomerValues-

UDFID, CustomerID, UDFValue

Definitions-

UDFID, Name, FieldType

Examples:

Client A (db A)

CustomerValues-

UDFID, CustomerID, UDFValue

11 , 123456 , Apples

Definitions-

UDFID, Name, FieldType

11 , Fruits , STR

Client B (db B)

CustomerValues-

UDFID, CustomerID, UDFValue

11 , 333444 , 112233

Definitions-

UDFID, Name, FieldType

11 , Account , INT

I have researched many ways to try to accomplish making both datasets show up on a report. All of which require some hard-coded values; which

then renders the unversality null and void.

The last approach I tried was to have the fields put into a view where the outcome was:

CustomerID,UDF1,UDF1Name,UDF2,UDF2Name,UDF3,UDF3Name

However I ran into the lack of ability to dynamically name the dimensions/expressions.

The other requirement is that this report run as fast as possible, given that one clients UDF combinations result in a record set of over 6 million rows. They have 57 different UDFs and 300,000 clients; so the product of all 57 UDFs per each customer is a staggeringly large number of returns.

My DBA has said that we must find an answer to this problem, and is open to changing how we structure the UDF tables.

I would appreciate any ideas that lead to being able to have the same report (with zero differences) apply to two DBs using the UDFs from each client in the same way.

My boss has also authorized me to seek out any QV Consultants for hire who may be able to resolve this problem.

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Fri, 15 Mar 2013 18:33:23 GMT

There are two issues to solve here, loading the data and displaying the data.

For loading the data, you can load the definitions table then loop through each individual row in the defination table to load the values from the CustomerValues table for each type. By looping, you can load the data from the correct column. You can name the data column UDF<UDFID> (e.g., UDF11 for Fruits in Customer A). If the id values can be large, you will have to create a new field with the loop value to associate the id to the loop value

To display the data, you will create a table with as many expressions as the customer with the most UDF fields. Each UDF expression UDF<id> and the label will be =Only(UDFID=<id> Name) (or use loop value instead of id). If you have many values (more than you want to create manaually) you will need to create a macro to dynamically add the expression.

-Van

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Mon, 18 Mar 2013 21:57:58 GMT

Thank you for your reply vhuynh. I have not been back to check on answers since I have been nose-down trying to solve this issue. I have come up with a similar answer, but without the macro to dynamically create the expressions, as I am not exactly sure what you mean. I thought I would share what I have done so far and see if anyone has a more elegant or more efficient solution:

I create a table (see below) with a column for each customers UDFname and UDFValue for each UDF on a client.

I then select those into a table to load into the QVW and use for the Dimensions/Expressions the UDFValue while the name is the UDFName. Then to show/hide each UDF I take the total possible values into another table and use the following for the Presentation of each UDF field [ =SubStringCount(GetFieldSelections(UDFName,',',Count(Distinct UDFName)),UDF20Name) ]. That way whenever a field is selected to been shown from the ListBox the column in the table will be shown/hidden based on the name the client recognizes rather than the UDFName label.

My only problem now is saving the selections from one load to another when we use QV server to QV Publisher to a display in our page. The clients are not seeing the QVW, rather a translated copy, so we loose the bookmarks whenever the report is re-loaded.

This is from the Load Statement in the report::

SQL

DECLARE @colsName NVARCHAR(2000)

SELECT @colsName = COALESCE(@colsName + ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Value] varchar(50)'+ ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Name] varchar(50)',

'[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Value] varchar(50)'+ ',[UDF' + CAST(UDFDefIds AS VARCHAR(10)) + 'Name] varchar(50)')

FROM dbo.UDFDefs

ORDER BY UDFDefIds

DECLARE @zsql NVARCHAR(4000)

SET @zsql = '

CREATE TABLE ##UDFMaster (CustID INT, ' + @colsName + '

)

EXECUTE(@zsql)

INSERT INTO ##UDFMaster (CustID)

SELECT DISTINCT CustID

FROM UDFCustVal

-- POPULATE UDFValues

DECLARE @c CURSOR

DECLARE @UDFid INT

DECLARE @UDFMasterField NVARCHAR(1000)

DECLARE @sql NVARCHAR(1000)

SET @c = CURSOR FOR

SELECT UDFDefIds

FROM dbo.UDFDefs

OPEN @c

FETCH NEXT

FROM @c INTO @UDFid

WHILE @@FETCH_STATUS = 0

BEGIN

SET @UDFMasterField = CAST('##UDFMaster'+'.'+'UDF'+CAST(@UDFid AS NVARCHAR(1000))+'Value' AS NVARCHAR(1000))

SET @sql = '

UPDATE

##UDFMaster

SET

'+@UDFMasterField+' = UDFCustVal.UDFValue

FROM

##UDFMaster

INNER JOIN

dbo.UDFCustVal

ON ##UDFMaster.CustID = UDFCustVal.CustID

WHERE

UDFCustVal.UDFDefIds = '+CAST(@UDFId AS NVARCHAR(1000))

--PRINT @##UDFMasterField

--PRINT @sql

EXEC sp_executesql @sql

FETCH NEXT FROM @c INTO @UDFid

END

CLOSE @c

DEALLOCATE @c

-- POPULATE UDFNames

DECLARE @c2 CURSOR

DECLARE @UDFId2 INT

DECLARE @UDFMasterField2 NVARCHAR(1000)

DECLARE @sql2 NVARCHAR(1000)

SET @c2 = CURSOR FOR

SELECT UDFDefIds

FROM dbo.UDFDefs

OPEN @c2

FETCH NEXT

FROM @c2 INTO @UDFid2

WHILE @@FETCH_STATUS = 0

BEGIN

SET @UDFMasterField2 = CAST('##UDFMaster'+'.'+'UDF'+CAST(@UDFid2 AS NVARCHAR(1000))+'Name' AS NVARCHAR(1000))

SET @sql2 = '

UPDATE

##UDFMaster

SET

'+@UDFMasterField2+' = UDFDefs.UDFName

FROM

UDFDefs

WHERE

UDFDefs.UDFDefIds = '+CAST(@UDFId2 AS NVARCHAR(1000))

--PRINT @##UDFMasterField

--PRINT @sql2

EXEC sp_executesql @sql2

FETCH NEXT FROM @c2 INTO @UDFid2

END

CLOSE @c2

DEALLOCATE @c2

SELECT * FROM ##UDFMaster ORDER BY CustID

;

NEWUDFs:

LOAD

CustID,

UDF1Value , UDF1Name ,

UDF2Value , UDF2Name ,

UDF3Value , UDF3Name ,

UDF4Value , UDF4Name ,

UDF5Value , UDF5Name ,

UDF6Value , UDF6Name ,

UDF7Value , UDF7Name ,

UDF8Value , UDF8Name ,

UDF9Value , UDF9Name ,

UDF10Value , UDF10Name ,

UDF11Value , UDF11Name ,

UDF12Value , UDF12Name ,

UDF13Value , UDF13Name ,

UDF14Value , UDF14Name ,

UDF15Value , UDF15Name ,

UDF16Value , UDF16Name ,

UDF17Value , UDF17Name ,

UDF18Value , UDF18Name ,

UDF19Value , UDF19Name ,

UDF20Value , UDF20Name ,

UDF21Value , UDF21Name ,

UDF22Value , UDF22Name ,

UDF23Value , UDF23Name ,

UDF24Value , UDF24Name ,

UDF25Value , UDF25Name ,

UDF26Value , UDF26Name ,

UDF27Value , UDF27Name ,

UDF28Value , UDF28Name

;

SELECT CustID,

UDF1Value , UDF1Name ,UDF2Value , UDF2Name ,

UDF3Value , UDF3Name ,UDF4Value , UDF4Name ,

UDF5Value , UDF5Name ,UDF6Value , UDF6Name ,

UDF7Value , UDF7Name ,UDF8Value , UDF8Name ,

UDF9Value , UDF9Name ,UDF10Value , UDF10Name ,

UDF11Value , UDF11Name ,UDF12Value , UDF12Name ,

UDF13Value , UDF13Name ,UDF14Value , UDF14Name ,

UDF15Value , UDF15Name ,UDF16Value , UDF16Name ,

UDF17Value , UDF17Name ,UDF18Value , UDF18Name ,

UDF19Value , UDF19Name ,UDF20Value , UDF20Name ,

UDF21Value , UDF21Name ,UDF22Value , UDF22Name ,

UDF23Value , UDF23Name ,UDF24Value , UDF24Name ,

UDF25Value , UDF25Name ,UDF26Value , UDF26Name ,

UDF27Value , UDF27Name ,UDF28Value , UDF28Name

FROM ##UDFMaster

;

SQL DROP TABLE ##UDFMaster

;

SQL SELECT UDFDefIds,UDFName FROM UDFDefs

;

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 16:58:59 GMT

Will you be loading multiple customers, each with different udf fields, into the same qvw file? If not then you don't really need the UDFName fields since the values will be the same. Each copy of the qvw file will need a to load the the definition table specific to the customer.

If you need to load multiple customers into one qvw, then you will need to load the definition table with the customer ids.

One question I forgot to ask, since the qvw will be used for different customers (whether its one copy or multiple copies) the only thing you can do with the UDF fields is to report on them since there's no way to know what calculations make sense for a particular UDF field.

I've attached an example qvw that may help.

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 19:21:43 GMT

Thank you vhuynh,

We will have multiple clients, who have multiple customers. So, each client has a unique database with many customers; however each client's udf setup is unique to them. The project is to have one report (copied verbatim to each client) that can be used on any db with any client and any combination of UDFs. The main problems come from not being able to pre-load like with Crystal or any other type of straight loading software. Because QV requires all the data at load time, I had to come up with a way to not have to explicitly name the fields. The solution above is only workable if every client only has a set amount of UDFs, so that the load statement is not broken by looking for UDFxName column when no UDFxName column exists.

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 19:32:15 GMT

You can add conditional statements to only show the column if field has been loaded. Also, the set analysis in the example before can affect performance if there are many UDF fields so you can use variables instead and set the variable values during the script load.

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 20:39:07 GMT

Thank you very much vhuynh,

Both of your examples did work by themselves. I can't say I completely understand what your are doing, but it is a faster loading solution and is better than what I came up with. I really appreciate your work and effort on this one.

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 21:53:29 GMT

Something that does happen is when we have larger clients with larger customer sets (327,000) with larger UDF ranges (57) any of the reports when just 1 udf is included; the allocated memory fails. Any tips on reducing the memory impact?

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Tue, 19 Mar 2013 23:08:14 GMT

It seems that the join in the script load is not necessary and will create performance issues if there are many udf fields. Also, duplicate rows are created if there are multiple udf values for a particular udfid and customer. You can replace the if statement containing the join with the following. This should perform much better.

UDFValues$(vUDFieldId):

NoConcatenate

Load CustomerID, UDFValue as UDFField$(vUDFieldId) resident SQLValues where UDFID = $(vUDFID);

Re: Ad-Hoc Variable Field Names and Values in Dimensions/Expressions

Wed, 20 Mar 2013 18:54:42 GMT

vhuynh, could not do it without you. The script worked fine. The report is performing much better. It still has some slowness and tendency to have a memory problem; but that it because the UDFs are 6.5 million rows (when loaded only once). However, I think at this point I can tell the requestor the slowness is in the amount of data, not the report's abilities.