Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
What's wrong with my expression?
Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')" or null}>}[Student ID])
I am trying to count Student ID where their Dealer Hire Date is Less Than or Equal to 12/31/2015 AND they either have a Dealer Term Date in-between the dates of 01/01/2015 - 12/31/2015 OR they don't have a Dealer Term Date.
My KPI object keeps returning the value of 0. I have a feeling my problem fall within how I am writing in the expression 'OR NULL'.
Suggestions on how to write this?
Working on it now
Check the attached
Script:
LOAD RowNo() as Key,
"Student ID",
"Dealer Hire Date",
"Dealer Term Date"
FROM [lib://Lib/SunnyT2.xlsx]
(ooxml, embedded labels, table is Sheet1);
Expression:
Count({<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+
<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"}, Key = {"=Len(Trim([Dealer Term Date])) = 0"}>}[Student ID])
The basic idea here is that you need to get a unique identifier which is associated with a single row. Since Student ID is repeating, I cannot use that, so I created a unique identifier using RowNo() field. You may be able to create a unique field by combining Student ID with Dealer Code in the script which might make Dealer Term Date unique for this new field.
LOAD
"Market Code",
"Market Desc",
"Business Center Code",
"Business Center Desc",
"District Code",
"District Desc",
"Dealer Code - Active Franchise Only",
"Dealer Name",
"Sales Group Size",
"Student ID",
"First Name",
"Last Name",
"Dealer Hire Date",
"Dealer Term Date",
"Program Year Month (YYYYMM)",
"Position Code",
"Position Name",
"First Name"&' '&"Last Name" as Name
FROM [lib://Retention (cag_t1300lm)/Retention data.xlsx]
(ooxml, embedded labels, table is [Report 1]);
Where and how would I insert? Data load editor didn't like it. Should I create a new page?
How about like this:
LOAD RowNo() as Key, //-> Option1
AutoNumber("Dealer Name"&"Student ID") as Key2, //->Option2
"Market Code",
"Market Desc",
"Business Center Code",
"Business Center Desc",
"District Code",
"District Desc",
"Dealer Code - Active Franchise Only",
"Dealer Name",
"Sales Group Size",
"Student ID",
"First Name",
"Last Name",
"Dealer Hire Date",
"Dealer Term Date",
"Program Year Month (YYYYMM)",
"Position Code",
"Position Name",
"First Name"&' '&"Last Name" as Name
FROM [lib://Retention (cag_t1300lm)/Retention data.xlsx]
(ooxml, embedded labels, table is [Report 1]);
Sunny T - You've done it again!!!!!!!
You deserve 1,000,000 points!!!
with expression & load script.
Super cool.
I am glad we were finally able to get this done
Since this has been an extra long thread, I would suggest marking some of the response as helpful as well as this will help others who land on this page for answers to know what are some of the responses which are helpful. Please do not over do this as well, because it is important that only those response which were helpful are marked to maintain the value of helpful.
Qlik Community Tip: Marking Replies as Correct or Helpful
Thanks,
Sunny
I'm on it! Thanks again as always Sunny T!!!!!!
Thank you for taking time to mark helpful responses . You can call me Sunny
Thank you Sunny. You've saved me yet again. It doesn't go unnoticed.