How Do I Make a Composite Virtual Field in Qlikview Script?

[#1]

Hey All,

I'm connecting via ODBC to a SQL Server Star Schema.

We are storing order data that contains product features.  For a given product, there are feature categories (such as color for instance) that contain feature Values (such as Blue).  There are particular categories (Let's say color and size) that are important to be grouped together.  I need to add a column to every order record that contains the feature Values of these groups.

For instance, an order will then contain the following:

Order #         [Other Fields]        Feature Group

12345               ...........            Blue Large

12346               ...........            Blue Medium

My intuition was to use a subquery in the select statement, that inserts into this Feature Group column the values that are associated when Category = "Color" or "Size" in this case.  However, I don't think Subqueries are possible in Qlikview scripts.  Is this correct?

I need a general approach to this more than a definitive answer at this point. 

I'm new to Qlikview.

Thanks!

[Oleg_Troyansky]

Hi Keitz,

you can do it in a variety of ways, including sub-queries. Keep in mind that the SQL SELECT statement that you include in your QlikView script, is a database statement, not a QlikView statement, so to speak. It will support any SQL syntax that is supported by your database, including Sub-queries. The SQL SELECT wizard may not have a visual interface for it, but you can certainly build your SQL statement in your DB client and then copy and paste it into your QlikView script, and it should work.

Alternatively, you can perform this logic in QlikView (this is especially handy if you don't have full control over the SQL database). Specifically for your structure with "generic" attributes and values, check out the GENERIC LOAD in QlikView - it might be interesting for your needs.

Last thing, I'd like to question your initial requirement - why do you think that you have to store Colors and Sizes with your Orders, as opposed to keeping them in your Product Master dimension? You may, if you wish, but you certainly don't have to.

Check out my recently published book QlikView Your Business, you will find a lot of useful information there. It even uses a fashion manufacturer for all the business cases, including the concepts of Style, Size, and Color as the major attributes in fashion.

cheers,

Oleg Troyansky

[#3]

Thank you Oleg,

The reason we have this requirement, is that we would like to Filter our results in our dashboard by these Feature Groups.  We have categories that are outside of these groups, let's say Shape, that might not be included in these groups. 

Our customer requires that they can see the count of different shapes (Round or Square), filtered by Feature Group (Blue Medium).

Another way to say this:  "Show me the counts of orders by Shape that are classified as Blue Medium".

These combinations of features (Feature Groups as I've called them) are an internal business concept for us.

[Gysbert_Wassenaar]

I think you can create the feature groups as bookmarks. That allows you (and/or the users) to create the feature groups on the fly as needed.

If you want to go for a script solution then you could create a FeatureGroup table in the script that lists the feature groups for each order.

OrderID, [Feature Group Name], [Feature Group Value]

1234,     Color - Size,     Blue Large

1234,     Color - Style,    Green Sports

1235,     Color - Size,     Blue Medium

...etc

You'd create this table by running through the orders table once for each feature group you want to define. You could define the feature groups and the fields that make up the feature group in another table or in an excel file. That way you can load that table and then loop through its records to read the data per feature group from the orders table and add them to the FeatureGroup table.

[Oleg_Troyansky]

Hi Keitz,

what I was trying to say is that you don't necessarily have to store everything in the Fact table with your Orders. If your data is properly associated, you can keep these attributes in a Dimension table, such as Product Master.

I understand and agree that you need to extract these attributes out of your table of generic attributes.

[Colin-Albert]

As Oleg has suggested you do not need to have all you dimension data in the fact table, a star model in QlikView is the better approach.

This post by marcus_sommer‌ has several links that should help you.

Get started with developing qlik datamodels

[#7]

troyansky‌ gwassenaar‌

The data is presently stored in a snowflake/star schema.  For Order Features specifically, we are employing a bridge table in the following manner:

The interesting / difficult bit is, for these Feature Groups I've described above, the Groups we care about can vary based on Vehicle Line (in the Fact table).

So for a given Vehicle Line, we are caring about Feature Groups containing Categories (Family_X) of a specific nature.  In our discussion so far, an example of Family_X would be "Shape".  An example of WERS_C would be "Round".

My confusion lies in where to place this virtual representation of these Groups, such that I'm not having to dig through all the WERS_C to see if it matches a particular value "Round" and adding it to that matching Feature Group (Round, Blue).

Apologies for the abstract nature of my question.  This has all been very helpful however!  Thank you!

[Oleg_Troyansky]

It would be helpful to see a small example of your app with the data...

Generally speaking, I'd try to de-normalize the data and create individual fields for the categories that are important - Shape, Size, Color, etc... in individual fields.

While doing that, it's important to understand the relationships between the data elements - 1:1, 1:M, or M:M. Your data modelling decisions will depend on that understanding.

cheers,

Oleg Troyansky