topic Re: Grouping in Load Script in QlikView

Grouping in Load Script

jlampard40 — Fri, 15 Nov 2024 23:53:34 GMT

Hi guys - I need to group some fields into common groups, so that I can then use a list box to select in my dashboard.

The dimension is called: AD_Room

There are many outputs in this dimension but I need to group them by 'External' and 'Internal'.

The 'External' rooms are fields: CTMOB; MSPI; XSPI; CSPI; CTMOB2

The 'Internal' rooms are fields: RMR2; SMR1; SMR2; SMR3; SMR4; SMR5

All other rooms can be grouped as 'Other Rooms'.

How can I do this in the load script?? Thanks.

Re: Grouping in Load Script

Taoufiq_Zarra — Wed, 30 Sep 2020 10:09:05 GMT

@jlampard40 you can use Mapping table like this :

MapField: mapping load * inline [ FieldtoMap,Mapby CTMOB,External MSPI,External XSPI,External CSPI,External CTMOB2,External RMR2,Internal SMR1,Internal SMR2,Internal SMR3,Internal SMR4,Internal SMR5,Internal ]; YOURTABLE: load Applymap('MapField',FieldtoMap,'Other Rooms') as New_Field, ...

Re: Grouping in Load Script

jlampard40 — Wed, 30 Sep 2020 09:52:27 GMT

Where would I put this in the load script Taoufiq? This is my load script (shortened a little):

Directory;
LOAD
AD_Referring_Location as Referring_Location,
AD_Data as Activity_or_Demand,
AD_Booked,
AD_Room as Room,
AD_Reported_By as Reported_By,
AD_Demand_Group as Demand_Group,
AD_CY as Calendar_Year_R

FROM
[..\Python\DASH_AD.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Grouping in Load Script

Taoufiq_Zarra — Wed, 30 Sep 2020 10:07:43 GMT

@jlampard40 yes like :

MapField: mapping load * inline [ AD_Room,Mapby CTMOB,External MSPI,External XSPI,External CSPI,External CTMOB2,External RMR2,Internal SMR1,Internal SMR2,Internal SMR3,Internal SMR4,Internal SMR5,Internal ]; Table: Directory; LOAD AD_Referring_Location as Referring_Location, AD_Data as Activity_or_Demand, AD_Booked, AD_Room as Room, Applymap('MapField',AD_Room,'Other Rooms') as New_AD_Room, AD_Reported_By as Reported_By, AD_Demand_Group as Demand_Group, AD_CY as Calendar_Year_R FROM [..\Python\DASH_AD.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Grouping in Load Script

jlampard40 — Wed, 30 Sep 2020 12:27:50 GMT

Hi Taoufiq - that works really well. What if I want to add a condition to this grouping, so that some scanners are external, some internal, some are external inpatient, some are external outpatient.

The dimensions are AD_Room and AD_PatientType

So, as before, the 'External' rooms are fields: CTMOB; MSPI; XSPI; CSPI; CTMOB2

The 'Internal' rooms are fields: RMR2; SMR1; SMR2; SMR3; SMR4; SMR5

But I also want to have external inpatient which are: CTMOB; MSPI; XSPI; CSPI; CTMOB2 and patient type 'A' (from dimension AD_PatientType).

External Outpatient will be CTMOB; MSPI; XSPI; CSPI; CTMOB2 and patient type 'B' (from dimension AD_PatientType).

All other rooms can be grouped as 'Other Rooms' again.

Many thanks again!

Re: Grouping in Load Script

Taoufiq_Zarra — Tue, 06 Oct 2020 12:55:51 GMT

@jlampard40 sorry for the late, we don't receive notification if I'm not tagged.

can you share a sample data ? and the expected ?