Table link problem

[Anonymous]

I have two tables below,

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

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

[mdmukramali]

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

[Anonymous]

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!

[mdmukramali]

Hi Rachel,

Can you share the script?

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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]

Try

Resident [Custom workshop];

in last line