Skip to main content

Qlik

cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
MT4T
Creator
‎2023-09-07 11:55 PM

Applymap on multiple columns

Hello,

I have this table as mapping

map:
LOAD * INLINE [
lookup, substitute
-, 0
+, 1
];

and this table with data

[data_loaded]:
LOAD
    ASSY as Assy, //
    "READY FOR SALES" as ReadyForSales, //
    EOL as Eql, //
    "REGULAR/ PROMO" as Regular_Promo, //
    Brand, //
    SMART as Smart, //
    Category, //
    SEGMENT as Segment, //
    RES as Res,    //
    Backlight, //
    SYSTEM as System, //
    "COLOR GAMUT" as ColorGamut, //
    SIZE as Size, //
    "FRAME/
FRAMELESS" as Frame_Frameless, //
    "FACTORY DESIGN" as FactoryDesign, //
    "DESIGN SALES" as DesignSales, //
    GLOBALNAME as GlobalName, //
    "SHORT MODEL NAME" as ShortModelName, //
    SERIES as Series, //
     "LINE" as Line, //
     "PREDECESSOR SERIES" as PredecessorSeries, //
     VER as Ver, //
     TUNER as Tuner, //
EAN as Ean, //
     "LED LIGHT" as LedLight, //
     "COLOR" as Colour, //
     FINISH as Finish, //
     "BEZEL MATERIAL" as BezelMaterial, //
     "STAND ID" as StandId, //
     "STAND FINISH" as StandFinish, //
     VESA as Vesa, //
     "O/S" as O_S, //
     DVD as Dvd, //
    "UK FVP" as UK_fvp, //
    HbbTV as Hbb_Tv, //
    "12/24V SUPP. CARAVAN" as Supp_caravan_12_24V, //
     "ACTIVE MOTION" as ActiveMotion, //
     "MiniLED zones" as MiniLedZones, //
    "AQUOS/ SMOOTH MOTION - MEMC" as Aquos_smmoothmotion, //
    HDR10 as Hdr10, //
    "Dolby Vision HDR" as Dolby_vision_hdr, //
    "HDR HLG" as Hdr_hlg, //
     SMART2 as Smart2, //
    AV1, //
    "HEVC/ h.265   10-bit" as Hevc_h265_10bit, //
     "Google Assistant with microphone" as GoogleAssistant, //
     Chromecast, //
     Bluetooth, //
    MIRACAST as Miracast, //
    "ACE PRO Picture Processor" as Ace_pro_picture_processor, //
     "Hotel Mode" as HotelMode, //
    "DOLBY Audio" as Dolby_audio, //
    "DOLBY Atmos" as Dolby_atmos, //
    "AC-4" as Ac4, //
    "DTS-HD" as Dts_hd, //
    "DTS-X" as Dts_x, //
    "DTS Virtual:X" as Dts_virtualx, //
    "Harman-Kardon (JBL) SOUND" as HarmanKardonJbl, //
    "Sound Equilizer" as Sound_equilizer, //
    "Audio /Max" as AudioMax, //
    HDMI as Hdmi, //
    "HDMI 2.1" as Hdmi21, //
    VRR as Vrr, //
     USB as Usb, //
     "Micro USB" as MicroUSB, //
    "SD CARD" as SdCard, //
     SCART as Scart, //
     Component, //
     Composite, //
     "Audio-in" as AudioIn, //
     "Audio-out" as AudioOut, //
     "Digital Optical Audio Output" as DigitalOpticalAudioOutput, //
     "Detachable cable" as DetachableCable, //
    PLATFORM as Platform, //
     SoC as Soc, //
     "R/C" as R_C, //
    "R/C DESIGN" as R_C_design, //
    "R/C Vendor" as R_C_vendor, //
     "Speakers vendor" as SpeakersVendor, //
     "Speakers type" as SpeakersType, //
     "BMS Vendor" as BmsVendor, //
     "Power Board" as PowerBoard, //
     "Main Board" as MainBoard, //
     "Main Board Vendor" as MainBoardVendor //,
//     MEMO1,
//     MEMO2
FROM [lib://EMARK:DataFiles/SCEP_LineUp_2023_05.xlsx]
(ooxml, embedded labels, table is [2023_LineUp]);
 
I need to create loop or subroutine which loop throught all columns and applymap on cols which contains + or -.
This will be better for Qlik and Inphinity forms because - is treated as null()
 
I can not change data in excel because it is used for different purposes also.
 
Any ideas?
 
Thanks in advance!
Labels (2)
Labels
1,714 Views
1 Solution

Accepted Solutions
marcus_sommer
MVP
‎2023-09-08 08:54 AM

In response to MT4T

It's possible with a mapping but without loop-stuff or applymap() else applying the mapping on a global level against the system-tables. Here an example how it might be done:

m: mapping load * inline [
L, R
-, 0
+, 1
];

map F1, F2, F3 using m;

t: load * inline [
K, F1, F2, F3
1, -, -, +
2, +, -, -
3, -, -, -
4, +, +, +
];

But IMO it's not the best approach with your data because they have no normal record-structure else they are a crosstable. This kind of data-structure has so many disadvantages that's seldom sensible to keep them else it would be better to transform them per: The Crosstable Load - Qlik Community - 1468083 and on the remaining value-column you could use the applymap(). 

 

View solution in original post

1,663 Views
5 Replies
henrikalmen
Specialist II
‎2023-09-08 03:20 AM

Your table "map" only becomes an actual mapping table if you add the prefix "mapping", otherwise it's just a regular table. LIke so:

map:
MAPPING LOAD * INLINE [
lookup, substitute
-, 0
+, 1
];

That table will not be visible in the data model, and it will be dropped after script execution.

Mapping tables are used in combination with applymap() function. Read up on mapping tables and the applymap function, and you'll find you will not have to loop over the table.

1,682 Views
0 Likes
MT4T
Creator
‎2023-09-08 03:49 AM
Author

In response to henrikalmen

You have right with prefix "Mapping" but even after i read specification of applymap() I have no clue how to use this without loop or sub. Of coures I can use applymap() on every column separate and it will work for sure.

But naming of theese columns changing very often with each release of "Line up" table and I try to build script which will be indestructible forever. 

Bellow is my script but i still have some issue with calling sub for columns in variable

// Load the map
MAPPING LOAD * INLINE [
lookup, substitute
-, 0
+, 1
];
 
// Load the data
[data_loaded]:
LOAD
    ASSY as Assy, //
    "READY FOR SALES" as ReadyForSales, //
    EOL as Eql, //
    "REGULAR/ PROMO" as Regular_Promo, //
    Brand, //
    SMART as Smart, //
    Category, //
    SEGMENT as Segment, //
    RES as Res,    //
    Backlight, //
    SYSTEM as System, //
    "COLOR GAMUT" as ColorGamut, //
    SIZE as Size, //
    "FRAME/
FRAMELESS" as Frame_Frameless, //
    "FACTORY DESIGN" as FactoryDesign, //
    "DESIGN SALES" as DesignSales, //
    GLOBALNAME as GlobalName, //
    "SHORT MODEL NAME" as ShortModelName, //
    SERIES as Series, //
     "LINE" as Line, //
     "PREDECESSOR SERIES" as PredecessorSeries, //
     VER as Ver, //
     TUNER as Tuner, //
EAN as Ean, //
     "LED LIGHT" as LedLight, //
     "COLOR" as Colour, //
     FINISH as Finish, //
     "BEZEL MATERIAL" as BezelMaterial, //
     "STAND ID" as StandId, //
     "STAND FINISH" as StandFinish, //
     VESA as Vesa, //
     "O/S" as O_S, //
     DVD as Dvd, //
    "UK FVP" as UK_fvp, //
    HbbTV as Hbb_Tv, //
    "12/24V SUPP. CARAVAN" as Supp_caravan_12_24V, //
     "ACTIVE MOTION" as ActiveMotion, //
     "MiniLED zones" as MiniLedZones, //
    "AQUOS/ SMOOTH MOTION - MEMC" as Aquos_smmoothmotion, //
    HDR10 as Hdr10, //
    "Dolby Vision HDR" as Dolby_vision_hdr, //
    "HDR HLG" as Hdr_hlg, //
     SMART2 as Smart2, //
    AV1, //
    "HEVC/ h.265   10-bit" as Hevc_h265_10bit, //
     "Google Assistant with microphone" as GoogleAssistant, //
     Chromecast, //
     Bluetooth, //
    MIRACAST as Miracast, //
    "ACE PRO Picture Processor" as Ace_pro_picture_processor, //
     "Hotel Mode" as HotelMode, //
    "DOLBY Audio" as Dolby_audio, //
    "DOLBY Atmos" as Dolby_atmos, //
    "AC-4" as Ac4, //
    "DTS-HD" as Dts_hd, //
    "DTS-X" as Dts_x, //
    "DTS Virtual:X" as Dts_virtualx, //
    "Harman-Kardon (JBL) SOUND" as HarmanKardonJbl, //
    "Sound Equilizer" as Sound_equilizer, //
    "Audio /Max" as AudioMax, //
    HDMI as Hdmi, //
    "HDMI 2.1" as Hdmi21, //
    VRR as Vrr, //
     USB as Usb, //
     "Micro USB" as MicroUSB, //
    "SD CARD" as SdCard, //
     SCART as Scart, //
     Component, //
     Composite, //
     "Audio-in" as AudioIn, //
     "Audio-out" as AudioOut, //
     "Digital Optical Audio Output" as DigitalOpticalAudioOutput, //
     "Detachable cable" as DetachableCable, //
    PLATFORM as Platform, //
     SoC as Soc, //
     "R/C" as R_C, //
    "R/C DESIGN" as R_C_design, //
    "R/C Vendor" as R_C_vendor, //
     "Speakers vendor" as SpeakersVendor, //
     "Speakers type" as SpeakersType, //
     "BMS Vendor" as BmsVendor, //
     "Power Board" as PowerBoard, //
     "Main Board" as MainBoard, //
     "Main Board Vendor" as MainBoardVendor //,
FROM [lib://EMARK:DataFiles/SCEP_LineUp_2023_05.xlsx]
(ooxml, embedded labels, table is [2023_LineUp]);
 
// List of column names to apply the map to
Let ColumnsToApplyMap = 'ReadyForSales,Eql,Regular_Promo,Smart,Res,Backlight,System,ColorGamut,Size,Frame_Frameless,FactoryDesign,DesignSales,GlobalName,ShortModelName,Series,Line,PredecessorSeries,Ver,Tuner,Ean,LedLight,Colour,Finish,BezelMaterial,StandId,StandFinish,Vesa,O_S,Dvd,UK_fvp,Hbb_Tv,Supp_caravan_12_24V,ActiveMotion,Aquos_smmoothmotion,Hdr10,Dolby_vision_hdr,Hdr_hlg,Smart2,AV1,Hevc_h265_10bit,GoogleAssistant,Chromecast,Bluetooth,Miracast,Ace_pro_picture_processor,HotelMode,Dolby_audio,Dolby_atmos,Ac4,Dts_hd,Dts_x,Dts_virtualx,HarmanKardonJbl,Sound_equilizer,AudioMax,Hdmi,Hdmi21,Vrr,Usb,MicroUSB,SdCard,Scart,Component,Composite,AudioIn,AudioOut,DigitalOpticalAudioOutput,DetachableCable,Platform,Soc,R_C,R_C_design,R_C_vendor,SpeakersVendor,SpeakersType,BmsVendor,PowerBoard,MainBoard,MainBoardVendor';
 
// Loop through the list and apply the map to the specified columns
For Each ColumnName In $(ColumnsToApplyMap)
    // Call the subroutine for each column
    Call ApplyMapToColumn('$(ColumnName)');
Next
 
// Drop the temporary table
DROP TABLE [data_loaded];
1,676 Views
0 Likes
marcus_sommer
MVP
‎2023-09-08 08:54 AM

In response to MT4T

It's possible with a mapping but without loop-stuff or applymap() else applying the mapping on a global level against the system-tables. Here an example how it might be done:

m: mapping load * inline [
L, R
-, 0
+, 1
];

map F1, F2, F3 using m;

t: load * inline [
K, F1, F2, F3
1, -, -, +
2, +, -, -
3, -, -, -
4, +, +, +
];

But IMO it's not the best approach with your data because they have no normal record-structure else they are a crosstable. This kind of data-structure has so many disadvantages that's seldom sensible to keep them else it would be better to transform them per: The Crosstable Load - Qlik Community - 1468083 and on the remaining value-column you could use the applymap(). 

 

1,664 Views
MT4T
Creator
‎2023-09-08 09:18 AM
Author

In response to marcus_sommer

This is such a simple approach I did not think about this in that way. Extremely useful in that case!🤑

But I have another question regarding this.

Once I do crossable and applymap(). Is there a way to return to initial format and pivot table back?😇

I love to use this method in power query-based tools. 

 

 

1,661 Views
0 Likes
marcus_sommer
MVP
‎2023-09-08 09:32 AM

In response to MT4T

The reverse way and pivoting data is also possible per The Generic Load - Qlik Community - 1473470 whereby it has one step more (joining within a loop) if really a complete table should be returned - otherwise each attribute would have an own table.

Better is usually just to use a pivot-chart within the UI and applying the created category-dimensions (means I would add at least one upper-categorize) horizontally.    

1,655 Views
Top