topic Re: How to load a new table from existing one using for each loop when the source table? in App Development

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

im_officer — Mon, 17 Aug 2020 17:14:42 GMT

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.

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

Saravanan_Desingh — Mon, 17 Aug 2020 23:17:26 GMT

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;

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

Saravanan_Desingh — Mon, 17 Aug 2020 23:18:10 GMT

Output.

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

Saravanan_Desingh — Tue, 18 Aug 2020 02:30:29 GMT

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;

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

Saravanan_Desingh — Tue, 18 Aug 2020 02:32:16 GMT

Output.