Nifty trick for INLINE tables

Here’s a nifty little trick I discovered by accident when creating INLINE tables…

Some Motivation

Typically when I’m creating an INLINE table it’s for one of two reasons:

1. I’m working very quickly and need to generate some additional data.
2. I’m building a control table to use as part of the ETL script. For example, setting up a loop and I want to pull a value from the INLINE table into each loop iteration.

The technique I’m going to demonstrate is relevant to usage #1 above. In fact, it’s only useful if I’m going to base my INLINE table (in part) on data that already exist in my data model.

Consider this specific example:

• We are building a Global Sales Analysis application
• We have a Country field in our associative model
• We wish to also analyze sales by Region: {EMEA, APAC, Americas} … but we don’t have a data source for this

What’s the easiest way to “append” the Region onto each Country record …. ???

The Trick

In the past, when faced with the use case as described above, I would do the following:

1. Create a List Box on Country.
2. Then right-click and copy possible values.
3. Then paste those values into Excel for further manipulation. Or more typically paste them right into the INLINE load wizard.

BUT, there is a simpler way to achieve this, if you take note of a curious little “Tool” menu inside the Load INLINE table wizard…

Step One

Make sure the source field already exists in the data model. In our example it’s the Country field.

 

Step Two

Go into the Script Editor (ctrl-E) and go to the Inline table wizard:

Insert > Load Statement > Load Inline

 

Step Three

Check out the Tools menu inside the Inline Data Wizard window

 

Click on it and select the Document Data… option

 

Step Four

Locate the source field in the list of Available Fields. You will have an option to select All Values (this is what I always end up using) or take advantage of the associative model if selections have already been made in the dashboard.

 

Step Five

The wizard then populates the values from the source field into the first column of the table

 

Step Six

The second (and subsequent) columns can now be manually populated. Which in our use case is adding the Region {EMEA, APAC, Americas}

 

The Final Product

Clean it up and reload. And the final result should look like this:

 

I hope you find a chance to use this shortcut in your future projects.

Keep on Qlikin’

Bill