Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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
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];