Cross-table then concatenate producing inaccurate ... - Qlik Community

malradi88 · ‎2018-05-08

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

sunny_talwar · ‎2018-05-08

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

malradi88 · ‎2018-05-08

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

malradi88 · ‎2018-05-08

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

Cross-table then concatenate producing inaccurate results