Dynamic field name on load

[timmarsh]

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:

1. The contents of StatusType field, and
2. 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;

[Vegar · Accepted solution]

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/Scrip...

And even HICs blog post "The generic load" 

[timmarsh]

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!