Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

xiyuzw1113
New Contributor III

Table link problem

I have two tables below,

Capture 1.PNG

Capture-2.PNG

And the results I got so far is something like this:

Capture-3.PNG

Now I would like to replace all fields like Cus.Qx to relevant 'question content' which showed in the second table. Any good ideas?

7 Replies
mdmukramali
Valued Contributor II

Re: Table link problem

Hi,

Use the ApplyMap concept to join both tables based on [Survey type]&'-'&[Question no] Fields.


//Script:

Question_Map:

Mapping LOAD

[Survey type]&'-'&[Question no] as Survey_Question_No,

    [Question content]

FROM

(ooxml, embedded labels, table is Sheet1);

Sample:

LOAD Respondent_No,

    [Survey date],

    [Survey type],

    Event,

    Client,

    Contractor,

    [Question no],

    ApplyMap('Question_Map',[Survey type]&'-'&[Question no]) as [Question content],

    [Question value]

FROM

(ooxml, embedded labels, table is Sheet1);

xiyuzw1113
New Contributor III

Re: Table link problem

Hi thanks for your reply. Here is one point I am not really sure, the first table is not the original one, I got it by using the unpivoting function, so how can I write the script to make sure I read this one not the original one? Or, I need to write another script to finish the unpivoting first? Thanks!


And one more question, for the index table, actually it has several type of survey, each type has different question no and question content, in relation to different table. So when I want to achieve this similar function, could you give some suggestions? I kind of don't know how to start .......Thanks!

Highlighted
mdmukramali
Valued Contributor II

Re: Table link problem

Hi Rachel,

Can you share the script?

xiyuzw1113
New Contributor III

Re: Table link problem

Hi Mohammed,

Unfortunately there is nothing new in my data script right now except the default one......while I found there are some autogenerate script, is that what you mean?

xiyuzw1113
New Contributor III

Re: Table link problem

Question_Map:

  Mapping LOAD

  [Survey type]&'-'&[Question no] as Survey_Question_No,

      [Question content]

  FROM [lib://AttachedFiles/Index-question and level.xlsx]

  (ooxml, embedded labels, table is Sheet1);

  [Custom workshop]:

  CROSSTABLE ([Custom workshop.Question no],[Custom workshop.Question value],4)

  LOAD

        [Respondent_No] AS [Custom workshop. Respondent_No],

        [Event] AS [Custom workshop.event],

        [Client] AS [Client-Client],

        [Contractor] AS [Custom workshop.Contractor],

        [Q1] AS [Cus.Q1],

        [Q2] AS [Cus.Q2],

        [Q3] AS [Cus.Q3],

        [Q4] AS [Cus.Q4],

        [Q5] AS [Cus.Q5],

        [Q6] AS [Cus.Q6],

        [Q7] AS [Cus.Q7],

        [Q8] AS [Cus.Q8],

        [Q9] AS [Cus.Q9],

        [Q10] AS [Cus.Q10],

        [Q11] AS [Cus.Q11],

        [Q12] AS [Cus.Q12],

        [Q13] AS [Cus.Q13],

        [Q14_1] AS [Cus.Q14_1],

        [Q14_2] AS [Cus.Q14_2],

        [Q14-text] AS [Cus.Q14-text],

        [Q15_1] AS [Cus.Q15_1],

        [Q15_2] AS [Cus.Q15_2],

        [Q15-text] AS [Cus.Q15-text],

        [Q16] AS [Cus.Q16],

        [Q17] AS [Cus.Q17],

        [Q18] AS [Cus.Q18],

        [Q19] AS [Cus.Q19],

        [Programme] AS [sheet1-1. Programme],

        [start date]

  FROM [lib://AttachedFiles/Custom workshop evaluation process.xlsx]

  (ooxml, embedded labels, table is Sheet1);

Sample:

  LOAD [Custom workshop. Respondent_No],

       [start date],

      [Custom workshop.event],

      [Client-Client],

      [Custom workshop.Contractor],

      [Custom workshop.Question no],

      ApplyMap('Question_Map',[Survey type]&'-'&[Question no]) as [Question content],

      [Custom workshop.Question value]

  FROM [Custom workshop];

xiyuzw1113
New Contributor III

Re: Table link problem

I have attached the script, now I got some errors like this:

The following error occurred:

No qualified path for file: ***

The error occurred here:

Sample: LOAD [Custom workshop. Respondent_No], [start date], [Custom workshop.event], [Client-Client], [Custom workshop.Contractor], [Custom workshop.Question no], ApplyMap('Question_Map',[Survey type]&'-'&[Question no]) as [Question content], [Custom workshop.Question value] FROM [Custom workshop]

shraddha_g
Honored Contributor III

Re: Table link problem

Try

Resident [Custom workshop];

in last line

Community Browser