How to load a new table from existing one using for each loop when the source table?

[im_officer]

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.

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    Event, Field, Value of the Field
    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
];

Gen:
Generic
LOAD Event, 'Medication (TRUE)', Field
Resident tab1
Where Field Like 'Medication*' And Not Field Like '*Quantity' And [Value of the Field]='TRUE';

Gen:
Generic
LOAD Event, 'Quantity', [Value of the Field]
Resident tab1
Where Field Like 'Medication*' And Field Like '*Quantity';

Gen:
Generic
LOAD Event, 'Doctor', [Value of the Field]
Resident tab1
Where Field = 'Doctor';

Drop Table tab1;

[Saravanan_Desingh]

Output.

[Saravanan_Desingh]

With a small fix.

tab1:
LOAD * INLINE [
    Event, Field, Value of the Field
    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
];

Gen:
Generic
LOAD Event, 'Medication (TRUE)', Field
Resident tab1
Where Field Like 'Medication*' And Not Field Like '*Quantity' And [Value of the Field]='TRUE';

Gen:
Generic
LOAD Event, SubField(Field,' ',1) As [Medication (TRUE)],'Quantity', [Value of the Field]
Resident tab1
Where Field Like 'Medication*' And Field Like '*Quantity';

Gen:
Generic
LOAD Event, 'Doctor', [Value of the Field]
Resident tab1
Where Field = 'Doctor';

Drop Table tab1;

[Saravanan_Desingh]

Output.