topic Re: Using applymap to load dynamic field data into the correct field in QlikView

Using applymap to load dynamic field data into the correct field

Wed, 06 Mar 2013 11:01:15 GMT

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 ?

Re: Using applymap to load dynamic field data into the correct field

Gysbert_Wassenaar — Wed, 06 Mar 2013 12:24:21 GMT

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).

Re: Using applymap to load dynamic field data into the correct field

Wed, 06 Mar 2013 12:45:34 GMT

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.

Re: Using applymap to load dynamic field data into the correct field

Gysbert_Wassenaar — Wed, 06 Mar 2013 15:59:20 GMT

With a loop then. See attached qvw.

Re: Using applymap to load dynamic field data into the correct field

Wed, 06 Mar 2013 16:06:53 GMT

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 :