topic Re: LastValue? in QlikView

LastValue?

Fri, 27 Mar 2015 19:02:46 GMT

I have a file I have uploaded into qlikview that can have multiple responses for the same element. I am looking for a way to identify the last entered value - I believe LASTVALUE may be able to identify this for me but I am having trouble with the syntax due to if statements that need to be built into my script as well (this is necessary because there are also multiple elements that need to be distinguished). Any help would be much appreciated it. Below I have included an example of the data (I only want to return the last line) as well as my script:

Section Name	Documentation Date & Time	Element Description	Response Value- Free text
Pneumococcal and Influenza Evaluation	03/20/2015 10:46:00 PM	Pneumococcal Vaccine Status	Received after age 65 years
Pneumococcal and Influenza Evaluation	03/20/2015 11:33:00 PM	Pneumococcal Vaccine Status	Never received pneumococcal vaccine
Pneumococcal and Influenza Evaluation	03/21/2015 12:43:00 AM	Pneumococcal Vaccine Status	Never received pneumococcal vaccine
Pneumococcal and Influenza Evaluation	03/21/2015 02:44:00 AM	Pneumococcal Vaccine Status	Received after age 65 years
Pneumococcal and Influenza Evaluation	03/21/2015 02:50:00 AM	Pneumococcal Vaccine Status	Never received pneumococcal vaccine
Pneumococcal and Influenza Evaluation	03/21/2015 03:08:00 AM	Pneumococcal Vaccine Status	Received after age 65 years
Pneumococcal and Influenza Evaluation	03/21/2015 08:29:00 AM	Pneumococcal Vaccine Status	Received after age 65 years
Pneumococcal and Influenza Evaluation	03/25/2015 09:34:00 PM	Pneumococcal Vaccine Status	Received after age 65 years
Pneumococcal and Influenza Evaluation	03/26/2015 04:19:00 PM	Pneumococcal Vaccine Status	Received after age 65 years

Documentation:
LOAD [Financial Number] as FIN,
[Admit Date & Time] as AdmitDate,
[Person Name- Full] as PatientName,
[Person Location- Facility (Curr)] as Facility,
[Person Location- Nurse Unit (Curr)] as Unit,
//[Form Description],
//[Section Name],
[Documentation Date & Time] as Completed,
//[Element Description],
//[Response Value- Free text]
if([Element Description]='Influenza Vaccine Status'
,[Response Value- Free text]) as FluVaccineStatus,
if([Element Description]='Pneumococcal Vaccine Status'
,[Response Value- Free text]) as PnVaccineStatus,
if([Element Description]='Reg PN Influenza Eligibility'
,[Response Value- Free text]) as FluEligibility,
if([Element Description]='Reg PN Influenza Not Given Reason vB'
,[Response Value- Free text]) as FluContraindications,
if([Element Description]='Reg PN Influenza Vaccine Refusal vA'
,[Response Value- Free text]) as FluVaccineRefusal,
if([Element Description]='Reg PN Pneumo Contraindication Reason vD'
,[Response Value- Free text]) as PnContraindications,
if([Element Description]='Reg PN Pneumococcal Vaccine Refusal'
,[Response Value- Free text]) as PnVaccineRefusal,
if([Element Description]='Reg PN Pneumonia Eligibility'
,[Response Value- Free text]) as PnVaccineEligibility
FROM
[Source\PN & Flu Eval Form Usage.xls]
(biff, embedded labels, table is [Vaccine Form Documentation$]);

Re: LastValue?

ramoncova06 — Fri, 27 Mar 2015 23:28:08 GMT

there is more than a couple of ways to do this though

I am assuming that the data is in descending order

LOAD [Section Name],

LastValue([Documentation Date & Time]) as [Documentation Date & Time],

[Element Description],

LastValue([Response Value- Free text]) as [Response Value- Free text]

FROM

(ooxml, embedded labels, table is Sheet1)

group by [Section Name],[Element Description]

temp:

LOAD [Section Name],

[Documentation Date & Time] as [Documentation Date & Time],

[Element Description],

[Response Value- Free text] as [Response Value- Free text],

RowNo() as Row

FROM

(ooxml, embedded labels, table is Sheet1);

right join (temp)

load

max(Row) as Row,

[Element Description],

[Section Name]

Resident temp

group by [Element Description],

[Section Name];

would give you this result

Documentation Date & Time	Element Description	Response Value- Free text	Section Name
3/26/2015 4:19:00 PM	Pneumococcal Vaccine Status	Received after age 65 years	Pneumococcal and Influenza Evaluation

Re: LastValue?

maxgro — Sat, 28 Mar 2015 10:50:06 GMT

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

source: // replace with load from your excel

LOAD [Section Name],

[Documentation Date & Time],

[Element Description],

[Response Value- Free text]

FROM

[http://community.qlik.com/thread/157948]

(html, codepage is 1252, embedded labels, table is @1);

final:

load *,

if([Section Name]<>Peek([Section Name]),1,0) as LastFlag

resident source

order by [Documentation Date & Time] desc

;

DROP Table source;