Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have this data on an excel type sheet that I am pulling into Qlik Sense:
When I total the amount of "1" it works fine, but when I try and count the "R13" I always get 0 when I should get a total of 31. The expression I am using is the following:
=Count({<EScore= {'R1'}>} EScore)
I'm not really sure what it's not working.
Thank you in advance!
For the attached... this should work
Table:
CrossTable(DayDate, EScore, 26)
LOAD
Facility,
"Mod Year",
"Elev Id",
"Elevator Num",
"Type",
"Class",
"Num Landings",
"Year",
Quarter,
"Month",
"# Days Operational",
"% Days Operational (Up Time)",
"Main Fee",
"R1 > 24 Hrs",
"R2 Response > 60 min",
"R3 Response Entrapment > 15 min",
"R4 Response after hour > 2 hrs",
"R5 Response after hour with entrapment > 60 min",
"R6 Fail 3 Times within 30 days",
"R7 Annual Inspections POC > 30",
"R8 5 Yr Inspection POC > 30 days",
"R9 Deficiencies Not Corrected > 90 days",
"R10 Audit POC > 24 hrs",
"R11 Audit POC > 48 Hrs",
"R12 If R6 then Reocurrance 50% M fee",
"R13 Lack Parts > 24 Hrs",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"30",
"31"
FROM [lib://Lib/Elevator Performance April.xlsx]
(ooxml, embedded labels, table is [Elevator Performance April]);
Final:
LOAD *,
MakeDate(2018, 4, Trim(DayDate)) as ActualDate,
If(IsNull(EScore), 0, EScore) as New_Escore
Resident Table;
Drop Table Table;
DROP Field EScore;
Rename Field New_Escore TO EScore;
Shouldn't it say = "R13" instead of "R1"?
Within my data I have R13 and R1, regardless it is not working.
try
=Count({1<EScore= {'R1'}>} EScore)
How is table looking in Qlik it is a cross table Yes?
Nope doesn't work
Are you using CrossTable in the script?
Can you try this
=Count({<EScore= {"*R13*"}>} EScore)
Yes, I am using a crosstable in the script.
Nope that doesn't work either, could something be wrong within my crosstable?
Input:
CrossTable(DayDate,EScore,27)
LOAD
[2018 May Record ID],
Facility,
"Mod Year",
"Elev Id",
"Elevator Num",
"Type",
"Class",
"Num Landings",
"Year",
Quarter,
"Month",
"# Days Operational",
"% Days Operational (Up Time)",
"Main Fee",
"R1 > 24 Hrs",
"R2 Response > 60 min",
"R3 Response Entrapment > 15 min",
"R4 Response after hour > 2 hrs",
"R5 Response after hour with entrapment > 60 min",
"R6 Fail 3 Times within 30 days",
"R7 Annual Inspections POC > 30",
"R8 5 Yr Inspection POC > 30 days",
"R9 Deficiencies Not Corrected > 90 days",
"R10 Audit POC > 24 hrs",
"R11 Audit POC > 48 Hrs",
"R12 If R6 then Reocurrance 50% M fee",
"R13 Lack Parts > 24 Hrs",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"30",
"31"
RESIDENT [2018 May];
Drop Table [2018 May];
Final:
Load RecNo() as ID,
[2018 May Record ID],
Facility,
"Mod Year",
"Elev Id",
"Elevator Num",
"Type",
"Class",
"Num Landings",
"Year",
Quarter,
"Month",
"# Days Operational",
"% Days Operational (Up Time)",
"Main Fee",
"R1 > 24 Hrs",
"R2 Response > 60 min",
"R3 Response Entrapment > 15 min",
"R4 Response after hour > 2 hrs",
"R5 Response after hour with entrapment > 60 min",
"R6 Fail 3 Times within 30 days",
"R7 Annual Inspections POC > 30",
"R8 5 Yr Inspection POC > 30 days",
"R9 Deficiencies Not Corrected > 90 days",
"R10 Audit POC > 24 hrs",
"R11 Audit POC > 48 Hrs",
"R12 If R6 then Reocurrance 50% M fee",
"R13 Lack Parts > 24 Hrs",
MakeDate(2018,04,trim(DayDate)) as ActualDate,
If(isnull(EScore),0,EScore) as EScore
Resident Input;
Drop table Input;
Everything looks good to me... not sure... are you able to share a sample?