Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am new to Qlik, loving it thus far. I need to calculate my teams compliance over a time period.
Historically I have done this within excel using the following formula:
Note Day = If(Transfer date - Service Date<4,1,0)
This returns 1 for compliant and 0 for not allowing my to average the records and return a %
For example:
Sally
service id service date transfer date Note Days
1 10/1/2016 10/2/2016 1
2 10/3/2016 10/3/2016 1
3 10/4/2016 10/8/2016 0
4 10/4/2016 10/7/2016 1
I know that Sally is at 74% compliance because of the note days.
How would I achieve this within Qliksense and in turn set a Gauge visualization?
Appreciate the thoughts.
If(transferDate - serviceDate<4,1,0) as noteDay;
Meaning - Let's say you have a txt file like this:
service transfer
10/1/2016 10/2/2016
10/3/2016 10/3/2016
10/4/2016 10/8/2016
10/4/2016 10/7/2016
I would load it like this:
[test]:
Load
rowNo() as servceId,
*,
If(transferDate - serviceDate<4,1,0) as noteDay;
LOAD
Date(Date#([service])) as serviceDate,
Date(Date#([transfer])) as transferDate
FROM [lib://AttachedFiles/test.txt]
(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);
If(transferDate - serviceDate<4,1,0) as noteDay;
Meaning - Let's say you have a txt file like this:
service transfer
10/1/2016 10/2/2016
10/3/2016 10/3/2016
10/4/2016 10/8/2016
10/4/2016 10/7/2016
I would load it like this:
[test]:
Load
rowNo() as servceId,
*,
If(transferDate - serviceDate<4,1,0) as noteDay;
LOAD
Date(Date#([service])) as serviceDate,
Date(Date#([transfer])) as transferDate
FROM [lib://AttachedFiles/test.txt]
(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);
Oh, and about the gauge visualization - I put Avg(noteDay) as the measure, number formatting as custom w/ format pattern 0 % (or you can do 0.00% if you want) and in appearance I put range limits as 0 and 1 in the sheet. This returns 75% because 3/4 of numbers are ones. Not sure if it's what you meant. You said - 74%. I'm not an mvp.
Hi!
I am using web connectors for the data pull. Can I load the data this way still?
My initial try came back with an error.
Thanks,
Erick
I don't see why that would cause an issue. I made a quick web connector and sort of tested it, no error messages for me. I think you just had a syntax error or an unrelated error.
Unfortunately it isn't working. Not sure if it is the query or what but whenever it gets to the piece below it comes back with an error.
Query is...
LOAD
clientvisit_id,
*,
if(clientvisit.transfer_date-clientvisit.timein<4,1,0) as NoteDay;
LOAD
Date(Date#([clientvisit.timein])) as serviceDate,
Date(Date#([clientvisit.transfer_date])) as transferDate
FROM
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
Loading a single table goes from down -> up. You can't use data in a second load that isn't in the first one.
Meaning - Qlik doesn't understand what is clientvisit.transfer_date and clientvisit.timein because there is no such thing loaded in the first load. You must load using the same name as before! If(transferDate - serviceDate<4,1,0) as noteDay; There's no longer such a thing as clientvisit.transfer_date and clientvisit.timein because you haven't loaded it. And you don't have to.
While we're at it - I'm pretty sure it doesn't understand what clientvisit_id is either for this exact reason - you haven't loaded it! If it's a number in the excel sheet, then load it first! Meaning - on the lowest load. And all the following ones until you don't need it. (the star does that also) If it's a unique number that doesn't exist in the excel sheet, you must declare it - rowNo() as clientvisit_id,
LOAD
*,
if(transferDate - serviceDate<4,1,0) as NoteDay;
LOAD
clientvisit_id,
Date(Date#([clientvisit.timein])) as serviceDate,
Date(Date#([clientvisit.transfer_date])) as transferDate
FROM
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
TD;DL: It's like you gave qlik a sheet of paper and took it away from it before allowing it to read it.
Thanks for the feedback! I should of clarified but it is loaded, i was only sharing the portion of my query that wasn't working.
This is th full query
[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element_9f18d851-1206-ffef-77df-c152259a]:
LOAD [name],
[type],
[minOccurs],
[%Key_Envelope_04722DC39F37DE52]
FROM [lib://Client Visits (tbh_erickd)]
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element]);
[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_069f33a2-0e7c-217a-f34d-d0d7f2fb]:
LOAD [diffgr:id],
[msdata:rowOrder],
[client_id],
[cptcode],
[timein],
[emp_id],
[rate],
[units_of_svc],
[appr],
[clientvisit_id],
[program_id],
[visittype_id],
[location_id],
[billing_group_id],
[transfer_date],
[non_billable],
[pri_payer_id],
[payer_id],
[status],
[visittype],
[team_id],
[duration],
[service_amount],
[emp_name],
[timeout],
[visit_dateday],
[cpt_modifier1],
[%Key_Envelope_04722DC39F37DE52]
FROM [lib://Client Visits (tbh_erickd)]
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
[Envelope_a8d6a2f1-79d7-df9d-d849-44eeda3b]:
LOAD [xmlns:soap],
[xmlns:xsi],
[xmlns:xsd],
[Body/ExportDataSetResponse/xmlns],
[Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:msdata],
[Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:diffgr],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/id],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:xs],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:msdata],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/name],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:IsDataSet],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:UseCurrentLocale],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/minOccurs],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/maxOccurs],
[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/name],
[%Key_Envelope_04722DC39F37DE52]
FROM [lib://Client Visits (tbh_erickd)]
(XmlSimple, table is Envelope);
[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_14e6bca2-f212-e072-c299-bc9dd3bf]:
LOAD [program_id],
[program_code]
FROM [lib://Programs (tbh_erickd)]
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element_9f18d851-1206-ffef-77df-c152259a] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element];
RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_069f33a2-0e7c-217a-f34d-d0d7f2fb] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table];
RENAME TABLE [Envelope_a8d6a2f1-79d7-df9d-d849-44eeda3b] TO [Envelope];
RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_14e6bca2-f212-e072-c299-bc9dd3bf] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table-1];
LOAD
name
FROM [lib://C-employee (tbh_erickd)]
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element]);
LOAD
"diffgr:id",
"msdata:rowOrder",
emp_id,
external_id,
%Key_Envelope_04722DC39F37DE52
FROM [lib://C-employee (tbh_erickd)]
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
LOAD
"xmlns:soap",
"xmlns:xsi",
"xmlns:xsd",
"Body/ExportDataSetResponse/xmlns",
"Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:msdata",
"Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:diffgr",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/id",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:xs",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:msdata",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/name",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:IsDataSet",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:UseCurrentLocale",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/minOccurs",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/maxOccurs",
"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/name",
%Key_Envelope_04722DC39F37DE52
FROM [lib://C-employee (tbh_erickd)]
(XmlSimple, table is Envelope);
LOAD
Empno,
Employee,
"Code Description",
JuneHours,
JulyHours,
AugHours,
SepHours,
OctHours,
NovHours,
F10,
F11,
"Total Hours"
FROM [lib://AttachedFiles/Employee Hours.xlsx]
(ooxml, embedded labels, table is [Analysis (2)]);
LOAD
clientvisit_id,
*,
if(clientvisit.transfer_date-clientvisit.timein<4,1,0) as NoteDay;
LOAD
Date(Date#([clientvisit.timein])) as serviceDate,
Date(Date#([clientvisit.transfer_date])) as transferDate
FROM
(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');
DERIVE FIELDS FROM FIELDS [timein], [transfer_date], [timeout] USING [autoCalendar] ;