Dynamic field name on load

timmarsh — Fri, 10 Jul 2020 05:51:31 GMT

Hi all, sorry if this is a bit unclear.

I currently receive electronic referrals from GPs. Our receiving system lets staff update the status of the referral through its lifecycle. I want to measure the time each referral spends in each state. Each time we change the status, we generate a timestamped FACT - see attached dummy data. There is no PHI here, dummy only.

The timestamp field name is always the same irrespective of the status change field value. So I can't just subtract timestamp.

So one way I can think to measure this is to dynamically create a timestamp name based on the value of statustype. I would do this load, based on two things:

The contents of StatusType field, and
Fixed text eg "TimeStamp"

StatusType and has at present 20 values for status. 3 examples "Received", "BeingTriaged", "Processed".

So what I want to do is: Take the value of StatusType for each record, and prepend to "_TimeStamp"

This means I will end up with a range of field names (all time stamps) called for example

Received_TimeStamp
BeingTriaged_TimeStamp

In a table, I'll then display, for each referral ID, the TimeinReceived (BeingTriaged_Timestamp-Received_TimeStamp) and so on.

I've tried using some code to extract the num of rows in Facts then iterate with a variable and peek to get the statustype value, but I can't get it to work.

Let vNumRows = NoOfRows(FACTS); set vTimeStampNames=''; For i=0 to $(vNumRows) - 1 Let vTimeStampNames = Peek(StatusType, i, FACTS); Next i;

Re: Dynamic field name on load

Vegar — Fri, 10 Jul 2020 05:53:45 GMT

In order to de-normalise and pivot your data out to multiple new fields you can use GENERIC LOAD.

Take a look at the Qlik help : https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/Generic.htm

And even HICs blog post "The generic load"

Re: Dynamic field name on load

timmarsh — Fri, 10 Jul 2020 06:13:11 GMT

Vegar! THAT is awesome!

I spent 2 hrs trying to crack this and I solved it in 5 mins, including charting it, which is exactly what I wanted!

I'm still wrangling with the charting, I used this statement, so might have the attribute and value back to front, but I think not.

Thanks again mate, this is great!

topic Re: Dynamic field name on load in QlikView

Dynamic field name on load

Re: Dynamic field name on load

Re: Dynamic field name on load