Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
colinodonnel
Creator II
Creator II

Column Order in Data Manager and ApplyMap

Hello all,

When I load a table from the script into Qlik Sense, the column order changes.

This is important as I want to use this table as a Mapping Table as the wrong column is being mapped.

For example:

Load

Column 1,

Column 2,

Resident Table;

When I go into Data Manager, the Column order from the left is shown as Column 2, Column 1,

It does not seem to matter if I change the load order to Column 2, then Column 1.

Any ideas?

Thank you,

Colin

1 Solution

Accepted Solutions
marcus_sommer

I think this is caused through the fact that there are no matching values between both tables and then the applymap() returned without setting the third parameter for non-matching values the used lookup-value. You could check it with:

Applymap( 'Map', [Column 1], 'no matching value available') as NewColumn,

This meant you need to take a closer look on the data within your columns and clean and/or format/convert them appropriate, maybe with something like: trim(), keepchar/purgechar(), upper/lower() or num#(), date#() and so on - so that both side are the same.

- Marcus

View solution in original post

9 Replies
marcus_sommer

You could just use cut & paste within the script itself to adjust the order of the fields.

- Marcus

colinodonnel
Creator II
Creator II
Author

Do you mean change from:

Load

Column 1,

Column 2

Resident Table;

to

Load

Column 2,

Column 1

Resident Table;

If so, I have tried this but without success.

marcus_sommer

There is the mapping statement missing like:

Map:

Mapping Load

Column 2,

Column 1

Resident Table;

- Marcus

colinodonnel
Creator II
Creator II
Author

Indeed there is.

I have tried to make the problem "bitesize" so perhaps a bit more background is required.

I have a resident table as above which I intend to use as a Mapping Table.

But when I try to Map this table, the wrong column is mapped.

So yes, I have used:

Map:

Mapping Load

Column 2,

Column 1

Resident Table;

And in the main table:

MainTable:

Load

[Column 1],

Applymap( 'Map', [Column 1]) as NewColumn,

etc

But instead of getting Column 2 from the Mapping Table in NewColumn it inserts Column 1 again.

Puzzled.

Thanks,

Colin

marcus_sommer

The logic by mapping is reverse. The first column is always the lookup-value and the second the return-value completely independent how they are named. Further the table must have only two columns and an applymap() on this will only take the first matching (in case there are several ones they won't be considered).

- Marcus

colinodonnel
Creator II
Creator II
Author

Hi Marcus,

Yes and they are a great way of mapping information.

Normally I use inline tables.

This is the first time that I have tried to map from a resident load.

I would have thought that it is the same logic and process.

But no matter what I try (i.e. change the load order of the Resident Map table / go in Data Manager and swap the fields), the first column is mapped and not the second column.

colinodonnel
Creator II
Creator II
Author

Hi Marcus,

I have now been able to get this to work.

using the default value helped

i.e.  Applymap( 'Map', [Column 1], 1) as NewColumn,

I think the "error"  was caused by the Columns not matching (a data matching error) and it seems to have repeated the Column 1 (from the MainTable) as the Default. This is what threw me.

I will mark this as solved.

Thank you for your assistance.

marcus_sommer

I think this is caused through the fact that there are no matching values between both tables and then the applymap() returned without setting the third parameter for non-matching values the used lookup-value. You could check it with:

Applymap( 'Map', [Column 1], 'no matching value available') as NewColumn,

This meant you need to take a closer look on the data within your columns and clean and/or format/convert them appropriate, maybe with something like: trim(), keepchar/purgechar(), upper/lower() or num#(), date#() and so on - so that both side are the same.

- Marcus

colinodonnel
Creator II
Creator II
Author

Hi Marcus,

Yes it was exactly that.

Thank you very much.

Colin