Using applymap to load dynamic field data into the correct field

[#1]

I have incoming data that is not structured for all records which you can find in the xls attached.

It consist of MachineData for different MachineTypes.

The datafields in column C untill I contain info related to the machine.

However, The data is not structured in the same way for all machines. It is structured related to the field MachType.

For all MachTypes ABC, the engine type resides in field Optie1

For all MachTypes DEF, the engine type resides in field Optie2

For all MachTypes GHI, the engine type resides in field Optie3

My output field in Qlikview should be enginetype of course.

I thought of the following solution in the script :

map_enginetype:

MAPPING LOAD * INLINE [

map_MachType, Map_DynamicField,

'ABC', '01',

'DEF', '02',

'GHI', '03'

];

RawData:

LOAD MachNr,

     MachType,

     Optie1                              as DynamicField01,

     Optie2                              as DynamicField02,

     Optie3                              as DynamicField03,

     Optie4                              as DynamicField04,

     Decimal1                    as DynamicField05,

     Decimal2                    as DynamicField06,

     Decimal3                    as DynamicField07

FROM

(biff, embedded labels, table is Sheet1$);

Output:

LOAD

          MachNr                              as MachNr_output,

          MachType                    as MachType_output,

          applymap('map_enginetype', MachType, 'NoMapping')                    as MappedField,

          DynamicField&applymap('map_enginetype', MachType, '')          as MachEngineType

RESIDENT RawData;

Explanation :

Load the data with all 'dynamic' fields aliased towards a fix fieldname (DynamicField followed with a sequence (RawData)

Create a mapping table containing the MachTypes with their corresponding sequence (see map_enginetype)

Finally, load the data towards an output table where you build the MachEngineType field using the fix fieldname, followed with an applymap function.

I am getting an error that the field DynamicField does not exist. I have loaded data before using variables in field names DynamicField&'$(vsequence)' and that certainly works.

Any ideas ?

[Gysbert_Wassenaar]

You can't simultaneously retrieve a value from a field and combine that with the name of another field to retrieve data from yet another field. All values from the record are retrieved at the same time, so you can first get one value, calculate something based on that and use that to then retrieve another value.

You'll have to use a nested if here (or a pick-match combination).

[#3]

I did simplify the example as we are coping with +/- 35 machine types and 5 different attributes of a machine (engine type, number of tyres, ...).

Curious though about what you are calling pick - match combination. Does it simplify the scripting part ?

Writing a 35 nested if combination is feasible, but if you need to maintain that script afterwards (eg : new  machine types), it's far more complicated.

Any other suggestions are welcome.

The best approach at frist sight seemed to keep the structure somewhere general (mapping table) and use it during the load.

[Gysbert_Wassenaar · Accepted solution]

With a loop then. See attached qvw.

[#5]

We figured out a similar solution :

// This table contains the 'mapping' of the attribute (eg : engine type) towards the correct dynamic field

tt_map_enginetype:

LOAD * INLINE [

MachType, DynamicFieldSeq,

'ABC', '01',

'DEF', '02',

'GHI', '03'

];

map_enginetype:

MAPPING LOAD

MachType                    as map_MachType,

DynamicFieldSeq          as map_DynamicFieldSeq

RESIDENT tt_map_enginetype;

// You need to rename the different dynamic fields in your RawData table towards a fix name and a sequence

// This sequence corresponds with the dynamic field sequence of the table above

RawData:

LOAD MachNr,

     MachType,

     Optie1                      as DynamicField01,

     Optie2                      as DynamicField02,

     Optie3                      as DynamicField03,

     Optie4                      as DynamicField04,

     Decimal1                    as DynamicField05,

     Decimal2                    as DynamicField06,

     Decimal3                    as DynamicField07

FROM

(biff, embedded labels, table is Sheet1$);

// Load the raw data table and map the machine type to the correct 'DynamicField' fieldname

tt_Output:

LOAD            *,

          'DynamicField'&applymap('map_enginetype', MachType, '')          as EngineType_MappedSeq

RESIDENT RawData;

// We drop the RawData table to avoid synthetic keys

Drop Table RawData;

// Run through the raw data table for each MachType that needs to be translated

For i = 1 To NoOfRows('tt_map_enginetype');

          let vFieldName  = 'DynamicField' & Num($(i),'00');

          Output:

          Load

          MachNr,

          MachType,

          EngineType_MappedSeq,

          $(vFieldName)                                        As MachEngineType

          Resident tt_Output

          Where EngineType_MappedSeq = '$(vFieldName)';

Next;

Drop Table tt_Output;

Table looks like this :