7 Replies Latest reply: Nov 21, 2017 11:06 PM by Shraddha Gajare RSS

    Table link problem

    Rachel Zhong

      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?

        • Re: Table link problem
          Mohammed Mukram

          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

          [C:\Users\Qlik\Downloads\Index.xlsx]

          (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

          [C:\Users\Qlik\Downloads\Sample (1).xlsx]

          (ooxml, embedded labels, table is Sheet1);

            • Re: Table link problem
              Rachel Zhong

              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!

                • Re: Table link problem
                  Mohammed Mukram

                  Hi Rachel,

                   

                  Can you share the script?

                    • Re: Table link problem
                      Rachel Zhong

                      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?

                      • Re: Table link problem
                        Rachel Zhong

                        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];

                        • Re: Table link problem
                          Rachel Zhong

                          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]