topic Re: Lookup Table in QlikView

Lookup Table

lbunnell — Thu, 21 Jan 2016 20:54:19 GMT

I have two tables containing survey data Table 1) with columns for each survey question (e.g. [Q1], [Q2], [Q3], etc. (values are the scores 1-10 for the responses to the question) and Table 2) with the Keys the definition of the question with columns [ColumnName] (values Q1, Q2, Q3,) and [Definition] (values: 'How did you like our service?', 'Would you purchase again?', 'Would you recommend our products?' etc). I'm looking for a way to join the tables so that clicking on a Table 2 [Definition] value brings up the results in the associated Q column for that question from Table 1.

Re: Lookup Table

swuehl — Thu, 21 Jan 2016 21:38:59 GMT

Maybe just transform your Table 1 using CROSSTABLE LOAD:

CROSSTABLE( Question, Response,1 )

LOAD

Recno() as ID,

FROM YourQuestionTable;

assuming YourQuestionTable contains only the Q1, Q2, Q3 etc fields. If not, you need to adapt the Number of qualifiying fields in CROSSTABLE (..., N) and / or list the questions explicitely. Have a look at

The Crosstable Load

Re: Lookup Table

lbunnell — Mon, 25 Jan 2016 15:51:51 GMT

Maybe I'm not applying this correctly, but I'm not getting the Question Code values (Q1, Q2, Q3, etc) to line up with the actual questions. I'm trying to make the Q1, Q2, Q3 become the column headers with the Survey Questions under each column

CrossTable([Question Code],[Question])
Load [Column Name],[Survey Question]
Resident KeyTable;

the table looks like this:

Column Name	Survey Question
QN1	All things considered, how satisfied are you?
QN2	How likely are you to continue to do business with us?
QN3	If asked, how likely would you be to recommend us?

Re: Lookup Table

swuehl — Mon, 25 Jan 2016 17:09:17 GMT

If you have loaded the questions / responses like shown above, you don't need a crosstable load for the second table, just link the fields:

Load [Column Name] as Question, [Survey Question]
Resident KeyTable;

If this is not working, could you post some sample data for your two tables (could be just sample / mock up data)?

Re: Lookup Table

lbunnell — Mon, 25 Jan 2016 17:26:35 GMT

Sorry if I'm not explaining this very well. What I'm trying to do is match up this key table with the response table which has the Q1, Q2, Q3 as the column headers with the responses to each question in the column fields (a record of each response). In the presentation I'd like to have a table or chart (Table A) that shows the questions and responses and a separate table (Table B) the shows the corresponding questions. When a Question(s) is/are selected, the corresponding actual question would show up in the separate table. I don't want QV to concatenate the questions to the responses.

Re: Lookup Table

swuehl — Mon, 25 Jan 2016 17:44:51 GMT

It's easier to understand with some sample data / tables to look at:

CROSSTABLE( Question, Response,1 )

LOAD

Recno() as ID,

INLINE [

QN1, QN2, QN3

1,2,3

2,3,4

];

LOAD [Column Name] as Question, [Survey Question] INLINE [

Column Name, Survey Question

QN1, All things considered, how satisfied are you?

QN2, How likely are you to continue to do business with us?

QN3, If asked, how likely would you be to recommend us?

];

Now create a chart with dimension [Survey Question] and as expression for example

=Avg(Response)

Re: Lookup Table

lbunnell — Fri, 29 Jan 2016 14:17:42 GMT

Here are the tables with sample data. Due to the large number of records in the DataTable, creating an Inline table manually would be a laborious process. Is there a way to automate that?

Re: Lookup Table

swuehl — Fri, 29 Jan 2016 17:14:49 GMT

Sure, the INLINE table source I've used is just for demonstration. Just replace the INLINE with a FROM, e.g. for the KeyTable:

LOAD [Column Name] as Question, [Survey Question]

FROM YourKeyTable;

You just need to adapt the YourKeyTable table name and the field names to your table source data.

Re: Lookup Table

lbunnell — Fri, 29 Jan 2016 19:43:46 GMT

Here's what I'm trying and here's what I'm getting.

CrossTable(Question,Response,1)
Load
Recno() as ID,
Code as [Question],
Question as [Survey Question],
*

Resident KeyTable;

Re: Lookup Table

swuehl — Fri, 29 Jan 2016 21:21:53 GMT

Please have a look at my above sample.

You should apply the CROSSTABLE LOAD only on the data table, not the key table. Also please note that I am assuming your data table shows only Question columns with responses, no other columns.