3 Replies Latest reply: May 8, 2018 2:09 PM by Mohammed Al Radi RSS

    Cross-table then concatenate producing inaccurate results

    Mohammed Al Radi

      Dear Qlik Community,

       

      I hope this finds you well. I am currently preparing a school app and the section concerning 'weekly quizzes' is producing inaccurate results. I have three separate tables for three subjects (arabic, math, english). Each table was crosstabled to create a unified date field then concatenated to one another to form one 'weekly quiz table'. Please find enclosed the script:

       

      [weekly quizzes pre school math]:

      Crosstable ([Date], [scoremath],4)

      LOAD

      [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

      [الاسم] AS [preschool.الاسم],

      'weekly quizes prescohol' as flagpreschool,

      حساب as [preschool-رياضيات],

          "43027",

          "43034",

          "43041",

          "43048",

          "43055",

          "43062",

          "43080",

          "43083",

          "43088",

          "43097"

          

      FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

      (ooxml, embedded labels, table is [weekly quizzes]);

       

      [Date weekly quiz]

       

      Load

      [preschool.الرقم التسلسلي],

      'Math' as subjectflag,

      'preschool' & '+' & [preschool.الاسم] as Key,

      [preschool-رياضيات],

      Date#(Date) as finaldate,

      scoremath

          

      Resident [weekly quizzes pre school math];

      Drop table [weekly quizzes pre school math];

       

       

      [weekly quizzes pre school arabic]:

      Crosstable ([Date], [scorearabic],4)

      LOAD

      [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

      [الاسم] AS [preschool.الاسم],

      'weekly quizes prescohol' as flagpreschool,

      [عربي] as  [preschool.عربي],

          "43027",

          "43034",

          "43041",

          "43048",

          "43055",

          "43062",

          "43080",

          "43083",

          "43088",

          "43097"   

      FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

      (ooxml, embedded labels, table is [weekly quizzes]);

       

      concatenate ([Date weekly quiz])

       

      Load

      [preschool.الرقم التسلسلي],

      'Arabic' as subjectflag,

      'preschool' & '+' & [preschool.الاسم] as Key,

      [preschool.عربي],

      Date#(Date) as finaldate,

      scorearabic

          

      Resident [weekly quizzes pre school arabic];

      Drop table [weekly quizzes pre school arabic];

       

      [weekly quizzes pre school English]:

      Crosstable ([Date], [scoreenglish],4)

      LOAD

      [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

      [الاسم] AS [preschool.الاسم],

      'weekly quizes prescohol' as flagpreschool,

      [انجليزي] as [preschool.انجليزي],

          "43027",

          "43034",

          "43041",

          "43048",

          "43055",

          "43062",

          "43080",

          "43083",

          "43088",

          "43097"   

      FROM [lib://EM (alfanar-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

      (ooxml, embedded labels, table is [weekly quizzes]);

       

       

      concatenate ([Date weekly quiz])

       

      Load

      [preschool.الرقم التسلسلي],

      'english' as subjectflag,

      'preschool' & '+' & [preschool.الاسم] as Key,

      [preschool.انجليزي],

      Date#(Date) as finaldate,

      scoreenglish

          

      Resident [weekly quizzes pre school English];

      Drop table [weekly quizzes pre school English];

       

       

      Once loaded into one table, when I create a table or any other visualisation in the front end - I am getting the exact same scores for arabic, english, math. Same averages...same numbers even though each subject has different grades. I am not sure what the problem is. I checked the data source and everything seems normal. When I look at the scores themselves (without aggregations) (scoreenglish,scoremath,scorearabic) they are in date format in the front end (all the year 1899). I tried converting them to num but I still get the same results for each subject.

       

      Any help would much appreciated. Thank you for your support

       

      Best,

       

      Mohammed

        • Re: Cross-table then concatenate producing inaccurate results
          Sunny Talwar

          Try this

           

          [weekly quizzes pre school math]:

          Crosstable ([Date], [scoremath],4)

          LOAD

          [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

          [الاسم] AS [preschool.الاسم],

          'weekly quizes prescohol' as flagpreschool,

          حساب as [preschool-رياضيات],

              "43027",

              "43034",

              "43041",

              "43048",

              "43055",

              "43062",

              "43080",

              "43083",

              "43088",

              "43097"

            

          FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

          (ooxml, embedded labels, table is [weekly quizzes]);

           

          [Date weekly quiz]

           

          Load

          [preschool.الرقم التسلسلي],

          'Math' as subjectflag,

          'preschool' & '+' & [preschool.الاسم] as Key,

          [preschool-رياضيات],

          Date(Num#(Date)) as finaldate,

          scoremath

            

          Resident [weekly quizzes pre school math];

          Drop table [weekly quizzes pre school math];

           

           

          [weekly quizzes pre school arabic]:

          Crosstable ([Date], [scorearabic],4)

          LOAD

          [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

          [الاسم] AS [preschool.الاسم],

          'weekly quizes prescohol' as flagpreschool,

          [عربي] as  [preschool.عربي],

              "43027",

              "43034",

              "43041",

              "43048",

              "43055",

              "43062",

              "43080",

              "43083",

              "43088",

              "43097"

          FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

          (ooxml, embedded labels, table is [weekly quizzes]);

           

          concatenate ([Date weekly quiz])

           

          Load

          [preschool.الرقم التسلسلي],

          'Arabic' as subjectflag,

          'preschool' & '+' & [preschool.الاسم] as Key,

          [preschool.عربي],

          Date(Num#(Date)) as finaldate,

          scorearabic

            

          Resident [weekly quizzes pre school arabic];

          Drop table [weekly quizzes pre school arabic];

           

          [weekly quizzes pre school English]:

          Crosstable ([Date], [scoreenglish],4)

          LOAD

          [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

          [الاسم] AS [preschool.الاسم],

          'weekly quizes prescohol' as flagpreschool,

          [انجليزي] as [preschool.انجليزي],

              "43027",

              "43034",

              "43041",

              "43048",

              "43055",

              "43062",

              "43080",

              "43083",

              "43088",

              "43097"

          FROM [lib://EM (alfanar-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

          (ooxml, embedded labels, table is [weekly quizzes]);

           

           

          concatenate ([Date weekly quiz])

           

          Load

          [preschool.الرقم التسلسلي],

          'english' as subjectflag,

          'preschool' & '+' & [preschool.الاسم] as Key,

          [preschool.انجليزي],

          Date(Num#(Date)) as finaldate,

          scoreenglish

            

          Resident [weekly quizzes pre school English];

          Drop table [weekly quizzes pre school English];

            • Re: Cross-table then concatenate producing inaccurate results
              Mohammed Al Radi

              Dear Sunny,

               

              Thank you for your message. The actual problem is with the following fields:

               

              scoremath

              scorearabic

              scoreenglish

               

              these fields represent quiz grades and for some reason they are identical for each subject. When I review the data source it shows that the students score very differently in each subject but in Qlik sense, no matter what dimension I filter by the grades of each subject are identical to one another.

               

              Best,

               

              Mohammed

                • Re: Cross-table then concatenate producing inaccurate results
                  Mohammed Al Radi

                  I have tried to just include 1 table (the math one) and the scoremath field still includes dates for some reason even though the values in the datasheet are all numbers. It is a mixtures of dates and numbers and I think this might be one of the reasons why the scores for all subjects are the same:

                   

                  [weekly quizzes pre school math]:

                  Crosstable ([Date], [scoremath],4)

                  LOAD

                  [الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

                  [الاسم] AS [preschool.الاسم],

                  'weekly quizes prescohol' as flagpreschool,

                  حساب as [preschool-رياضيات],

                      "43027",

                      "43034",

                      "43041",

                      "43048",

                      "43055",

                      "43062",

                      "43080",

                      "43083",

                      "43088",

                      "43097"

                     

                  FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

                  (ooxml, embedded labels, table is [weekly quizzes]);

                   

                  [Date weekly quiz]

                   

                  Load

                  [preschool.الرقم التسلسلي],

                  'Math' as subjectflag,

                  'preschool' & '+' & [preschool.الاسم] as Key,

                  [preschool-رياضيات],

                  Date#(Date) as finaldate,

                  scoremath

                     

                  Resident [weekly quizzes pre school math];

                  Drop table [weekly quizzes pre school math];