Qlik Sense How to load a new table from existing one using for each loop when the source table is filled vertically?

Question

I have a table with the following structure:

The desired loaded table in Qlik Sense is as following:

BY this structure, I will be able to add a table showing each doctor, and how many medications he prescribed, and even break it down into detailed pivot table to show what are these meds:

I tried to loop over the initial table [Medications] (which is coming from a REST API, so we cannot change it before loading to desired form):

FOR Each ev in FieldValueList('Event')

[MedAndDoctors]:

LOAD 
    $(ev) as event_id,
    if (Field = 'Medication1' OR Field=Medication2 OR Field = Medication3 OR..., [Field]) AS med_name,
    if (Field = 'Doctor', [Field]) AS doctor_name,
    if (Field = 'Medication1 Quantity' OR Field = 'Medication2 Quantity' OR ..., [Field]) AS Quantity

RESIDENT ([Medications]);
WHERE event_id = '$(ev)';
Next ev;

Note that Field column contains lots of more info. Actually, the survey filled, is saved in a vertical structure, instead of the regular horizontal structure where all values of each event are on the same row.

The result was exactly the same of [Medications] table but with only the specified field values, so I couldn't display the desired output table.

score 3 · Answer 1 · answered Aug 18 '20 at 02:39

After executing the script below we will get two tables - Doctors and Medications

Doctors table will contain the following data:

And Medications:

Once we have the data in this format then its very easy to create the result table:

Raw:
Load * Inline [
Event, Field               , Value
Ev1  , Medication1         , TRUE
Ev1  , Medication2         , TRUE
Ev1  , Doctor              , XYZ
Ev1  , Medication1 Quantity, 13
Ev1  , Medication2 Quantity, 3
Ev2  , Medication1         , TRUE
Ev2  , Doctor              , ABC
Ev2  , Medication1 Quantity, 5
];


// List of Doctors by event
Doctors:
Load Distinct 
  Event,
  Value as Doctor
Resident
  Raw
Where
  Field = 'Doctor'
;


Medications:
// List of all medications names by event
Load Distinct 
  Event,
  Field as Medication  
Resident
  Raw
Where
      SubStringCount(Field, 'Medication') > 0
  and SubStringCount(Field, 'Quantity')   = 0
  and Value = 'TRUE'
;

join 

// List of medication quantities by event
Load Distinct 
  Event,
  trim(replace(Field, 'Quantity', '')) as Medication,
  Value as MedicationQuantity
Resident
  Raw
Where
      SubStringCount(Field, 'Medication') > 0
  and SubStringCount(Field, 'Quantity')   > 0
;

Drop Table Raw;

But there is more data then the one shown here, so should I load all of it inside the inline block? — alim1990, Aug 18 '20 at 05:35
I've displayed only few rows of the existing data but there is 22 000 rows in the horizontal initial table — alim1990, Aug 18 '20 at 05:41
Ive used inline load just to have some data. So you should not use the inline load from my example — Stefan Stoichev, Aug 18 '20 at 07:31

Qlik Sense How to load a new table from existing one using for each loop when the source table is filled vertically?

1 Answers1