25 Replies Latest reply: Dec 14, 2017 1:53 AM by pradosh thakur RSS

    Using Applymap to multiple excel files

    Stanley Cheong

      Hi, i'm looking to map columns from one excel file to multiple excel files in a 'For Loop' using the Applymap() function. Is there an example of how this could be achieved?

       

      E.g:

      Multiple Mapping.PNG

       

      Objective End Result:

      EndResult.PNG



      My code now for the Reports is something like:

      ------------------------------------------------

      FOR Each File1 in filelist ('lib://ExcelFiles/1 Templates\Report 2017*.xlsx')

       

      SumReportOct:

       

        LOAD

        "ExtractedValue",

        "ReturnedClusterName"

        "Compliance Score"

       

      FROM '$(File1)' (ooxml, embedded labels, table is [Overall_Score]);

      NEXT;

      ------------------------------------------------

       

       

       

      My code now for the Asset List is something like:

      ------------------------------------------------

       

      ClusterToClusterMapping:

      Mapping Load

      "ReturnedClusterName",

      "Compliance Score"

      Resident SumReportOct;

       

      AssetListComplianceScoreHostZ:

      Mapping Load Distinct

      "Extractedvalue",

      "Compliance Score"

      Resident SumReportOct;

       

      AssetList1:

       

      load*,

      ApplyMap('AssetListComplianceScoreHostZ',"HostNameFinal",ApplyMap('AssetListComplianceScoreHostZ',"Cluster Name"

      ,ApplyMap('ClusterToClusterMapping',"Cluster Name",'Unknown'))) as "Compliance Score Final"

      ;

       

      LOAD

      "HostNameFinal" as "HostNameFinal"

      "Cluster Name" as "Cluster Name"


      FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server);

       

      ------------------------------------------------

        • Re: Using Applymap to multiple excel files
          Varun Prakash Paulraj

          I beleive the Report_201705 is your excel file or QVD name?

           

          in this case use something like below to achieve the result

           

          Load

          [Host Name],

          [Compliance Score],

          Right(Filebasename(),5) as Month

           

          Report*.qvd(qvd);

            • Re: Using Applymap to multiple excel files
              Stanley Cheong

              Report_201705 would be the excel. Thanks for suggesting on how to take the first 5 characters from the right of the filename. But how can i do an Applymap() function to map the row "Host Name" in my asset list to all of the Reports and get the compliance score for each month?

                • Re: Using Applymap to multiple excel files
                  Marcus Sommer

                  I think you don't need an applymap() here just loading (maybe per explicit concatenate-statement) the files will be enough by adding the period like suggested from Varun.

                   

                  - Marcus

                    • Re: Using Applymap to multiple excel files
                      Stanley Cheong

                      Hi, i have updated my question, hopefully it would be better to understand why there's a need for ApplyMap().

                        • Re: Using Applymap to multiple excel files
                          Marcus Sommer

                          I think I would do something like this:

                           

                          mapHost: mapping load HostNameFinal, [Cluster Name]

                          FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server);

                           

                          mapCluster: mapping load [Cluster Name], HostNameFinal

                          FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server);

                           

                          FOR Each File1 in filelist ('lib://ExcelFiles/1 Templates\Report 2017*.xlsx')

                               final:

                               load

                                    ExtractedValue, [Compliance Score], right(filebasename(), 6) as YearMonth,

                                    applymap('mapHost', ExtractedValue,

                                         applymap('mapCluster', ExtractedValue, ExtractedValue)) as ExtractedValue2

                               from '$(File1)' (ooxml, embedded labels, table is [Overall_Score]);

                          next

                           

                          - Marcus

                            • Re: Using Applymap to multiple excel files
                              Stanley Cheong

                              Hi, thanks for suggesting this solution, but my base has to start by mapping the HostNameFinal to the ExtractedValue. Are there any other ways to go around this?

                                • Re: Using Applymap to multiple excel files
                                  Marcus Sommer

                                  Why? Did you try my suggestion and it really differ from your expected results? What are these differences?

                                   

                                  - Marcus

                                    • Re: Using Applymap to multiple excel files
                                      Stanley Cheong

                                      Hi, because the question that i've posted is just a scaled down example to what i am really doing. Your solution would achieve the same results, i would be able to find all of the HostNameFinal that exists along with the extractedvalue, but with what i am really working on, the HostNameFinal has HostNames that could not be found in the extractedvalue column and they have to be labeled to have an compliance score of 0.

                                       

                                      So i am assuming that's why i have to start by mapping the HostNameFinal to the ExtractedValue so that i can deal with the rest of the HostNameFinal that are not found.

                                        • Re: Using Applymap to multiple excel files
                                          Marcus Sommer

                                          Normally it's just the opposite what's needed - filling/cleaning/replacing values from dimension-tables to fact-tables and not reverse - therefore my suggestion.

                                           

                                          If the "Asset List" is not a dimension-table there may other ways to build a datamodel more useful maybe by just concatenating both tables and applying the cleaning-mapping if necessary on them.

                                           

                                          In your case I would probably keep my approach and if it's really needed to have all values from the "Asset List" I would just add them by a concatenating like this:

                                           

                                          concatenate(final)

                                          load HostNameFinal as ExtractedValue2, 0 as [Compliance Score]

                                          FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server)

                                          where not exists(ExtractedValue2, HostNameFinal);

                                           

                                          - Marcus

                          • Re: Using Applymap to multiple excel files
                            pradosh thakur

                            Hi

                             

                            PFA

                            Capture.PNG

                             

                             

                            regards

                            Pradosh

                              • Re: Using Applymap to multiple excel files
                                pradosh thakur

                                this is the code. Change the code a bit by replacing inline load with your table.

                                 

                                [Report 201705]:

                                LOAD * INLINE [

                                    ExtractedValue, Compliance Score

                                    BIEW01, 100

                                    ARKW01V, 98

                                    CRMAW01C, 95

                                ];

                                NoConcatenate

                                 

                                [Report 201706]:

                                LOAD * INLINE [

                                    ExtractedValue, Compliance Score

                                    BIEW01C, 95

                                    ARKW01V, 100

                                    CRMAW01V, 97

                                ];

                                NoConcatenate

                                 

                                [Report 201707]:

                                LOAD * INLINE [

                                    ExtractedValue, Compliance Score

                                    BIEW01, 98

                                    ARKW01C, 99

                                    CRMAW01V, 91

                                ];

                                 

                                 

                                 

                                for i = NoOfTables() - 1 to 0 step - 1

                                 

                                    let vTable = tablename($(i));

                                IF WildMatch('$(vTable)','Report*') then

                                   Load *, alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                   Right('$(vTable)',6) as Month INLINE [

                                    HostNameFinal, ClusterName

                                    BIEW01, BIEW01C

                                    ARKW01V, ARKW01C

                                    CRMAW01V, CRMAW01C

                                ];

                                 

                                drop table '$(vTable)';

                                endif

                                next i;

                                 

                                regards

                                Pradosh

                                  • Re: Using Applymap to multiple excel files
                                    Stanley Cheong

                                    Hi thanks for suggesting this solution, but how make it such in a way that it loops through all of the reports in the directory?

                                      • Re: Using Applymap to multiple excel files
                                        pradosh thakur

                                        You can use a for loop to load all the file with 'Report *'  than use the below loop to map them to Asset list table.

                                         

                                        regards

                                        Pradosh

                                          • Re: Using Applymap to multiple excel files
                                            Stanley Cheong

                                            Right, if i do a for loop for all of the reports, how would i be able to retrieve the date then?

                                             

                                            Here's my current code:

                                             

                                             

                                             

                                            FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                             

                                             

                                            [Reports]:

                                            LOAD

                                                ExtractedValue,

                                                "Compliance Score (%)" as "Compliance Score"

                                            FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                             

                                             

                                            for i = NoOfTables() - 1 to 0 step - 1

                                             

                                             

                                                let vTable = tablename($(i));

                                            IF WildMatch('$(vTable)','Report*') then

                                               Load *,

                                              

                                               alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

                                               Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                              

                                              Right('$(vTable)',6) as Month INLINE [

                                             

                                             

                                                HostNameFinal, ClusterName

                                                BIEW01, BIEW01C

                                                ARKW01V, ARKW01C

                                                CRMAW01V, CRMAW01C

                                             

                                             

                                            ];

                                             

                                            drop table '$(vTable)';

                                            endif

                                             

                                             

                                            next i;

                                            NEXT;

                                              • Re: Using Applymap to multiple excel files
                                                pradosh thakur

                                                Try this

                                                 

                                                dummy:

                                                load * inline[

                                                ExtractedValue, Compliance Score

                                                dummy, dummy

                                                ];

                                                 

                                                FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                                 

                                                 

                                                [Reports]:

                                                NoConcatenate

                                                LOAD

                                                    ExtractedValue,

                                                    "Compliance Score (%)" as "Compliance Score"

                                                FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                                 

                                                 

                                                NEXT;

                                                 

                                                 

                                                for i = NoOfTables() - 1 to 0 step - 1

                                                 

                                                 

                                                    let vTable = tablename($(i));

                                                IF WildMatch('$(vTable)','Report*') then

                                                   Load *,

                                                 

                                                   alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                   Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                                 

                                                  Right('$(vTable)',6) as Month INLINE [

                                                 

                                                 

                                                    HostNameFinal, ClusterName

                                                    BIEW01, BIEW01C

                                                    ARKW01V, ARKW01C

                                                    CRMAW01V, CRMAW01C

                                                 

                                                 

                                                ];

                                                 

                                                drop table '$(vTable)';

                                                endif

                                                 

                                                 

                                                next i;

                                                 

                                                 

                                                regards

                                                Pradosh

                                                  • Re: Using Applymap to multiple excel files
                                                    Stanley Cheong

                                                    Hi, i'm getting this as an result to your codes:

                                                     

                                                    qlikdate.PNG

                                                    It seems like the month is getting it's fields based on the table name that was created in the data load editor.

                                                     

                                                    FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                                    [Reports]: <-------- From here

                                                    NoConcatenate

                                                     

                                                     

                                                    LOAD

                                                        ExtractedValue,

                                                        "Compliance Score (%)" as "Compliance Score"

                                                    FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                                      • Re: Using Applymap to multiple excel files
                                                        pradosh thakur

                                                        Try this

                                                         

                                                        dummy:

                                                        load * inline[

                                                        ExtractedValue, Compliance Score

                                                        dummy, dummy

                                                        ];

                                                         

                                                        FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                                         

                                                         

                                                         

                                                        NoConcatenate

                                                        LOAD

                                                            ExtractedValue,

                                                            "Compliance Score (%)" as "Compliance Score"

                                                        FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                                         

                                                         

                                                        NEXT;

                                                         

                                                         

                                                        for i = NoOfTables() - 1 to 0 step - 1

                                                         

                                                         

                                                            let vTable = tablename($(i));

                                                        IF WildMatch('$(vTable)','Report*') then

                                                           Load *,

                                                         

                                                           alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                           Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                                         

                                                          Right('$(vTable)',6) as Month INLINE [

                                                         

                                                         

                                                            HostNameFinal, ClusterName

                                                            BIEW01, BIEW01C

                                                            ARKW01V, ARKW01C

                                                            CRMAW01V, CRMAW01C

                                                         

                                                         

                                                        ];

                                                         

                                                        drop table '$(vTable)';

                                                        endif

                                                         

                                                         

                                                        next i;

                                                         

                                                         

                                                        regards

                                                        Pradosh

                                                          • Re: Using Applymap to multiple excel files
                                                            Stanley Cheong

                                                            Hi, there will be a synthetic key if the table name is removed.

                                                            SynKey.PNG

                                                              • Re: Using Applymap to multiple excel files
                                                                pradosh thakur

                                                                You don't really need those table other than mapping right . Synthetic keys are not always harmful. if you don't want synthetic keys than  try using drop table dummy; at the last. if possible show me your data model.

                                                                 

                                                                 

                                                                 

                                                                regards

                                                                Pradosh

                                                                  • Re: Using Applymap to multiple excel files
                                                                    Stanley Cheong

                                                                    Hi, the synthetic key still appears even after dropping the dummy table or commenting the entire table out. Here's a picture of my data modelQlikDataModel.PNG

                                                                      • Re: Using Applymap to multiple excel files
                                                                        pradosh thakur

                                                                        Thats why i asked you to tweak your code.. please notice that you are using table name which is overall nor report*

                                                                         

                                                                        dummy:

                                                                        load * inline[

                                                                        ExtractedValue, Compliance Score , Month

                                                                        dummy, dummy, dummy

                                                                        ];

                                                                         

                                                                        FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                                                         

                                                                         

                                                                         

                                                                        NoConcatenate

                                                                        LOAD

                                                                            ExtractedValue,

                                                                            "Compliance Score (%)" as "Compliance Score",

                                                                            subfield(Right((filename()',10),'.') as Month

                                                                         

                                                                        FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                                                         

                                                                         

                                                                        NEXT;

                                                                         

                                                                         

                                                                        for i = NoOfTables() - 1 to 0 step - 1

                                                                         

                                                                         

                                                                            let vTable = tablename($(i));

                                                                        IF WildMatch('$(vTable)','Overall*') then

                                                                           Load *,

                                                                         

                                                                           alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                                           Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                                                        alt(Lookup('Month','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                                           Lookup('Month','ExtractedValue', ClusterName, '$(vTable)')) as Month

                                                                         

                                                                         

                                                                        INLINE [

                                                                         

                                                                         

                                                                            HostNameFinal, ClusterName

                                                                            BIEW01, BIEW01C

                                                                            ARKW01V, ARKW01C

                                                                            CRMAW01V, CRMAW01C

                                                                         

                                                                         

                                                                        ];

                                                                         

                                                                        drop table '$(vTable)';

                                                                        endif

                                                                         

                                                                         

                                                                        next i;

                                                                         

                                                                         

                                                                        regards

                                                                        Pradosh

                                                                          • Re: Using Applymap to multiple excel files
                                                                            Stanley Cheong

                                                                            It seems like the subfield and filename() is not working properly,

                                                                            filenamesubfieldtable.PNG

                                                                            This is how my reports look like.

                                                                            filename.PNG

                                                                              • Re: Using Applymap to multiple excel files
                                                                                pradosh thakur

                                                                                Try this 


                                                                                subfield(Right(filename(),11) ,'.') as Month

                                                                                or

                                                                                subfield(Right(filename(),11) ,'.',1) as Month

                                                                                  • Re: Using Applymap to multiple excel files
                                                                                    Stanley Cheong

                                                                                    Hi, i've used your subfield solution, but it seems like it still isn't working.

                                                                                    Stillnomonth.PNG

                                                                                     

                                                                                    Here's my current code:

                                                                                     

                                                                                     

                                                                                    [dummy]:

                                                                                     

                                                                                     

                                                                                    LOAD * INLINE [

                                                                                        ExtractedValue, Compliance Score

                                                                                        dummy, dummy

                                                                                    ];

                                                                                     

                                                                                     

                                                                                    FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

                                                                                     

                                                                                     

                                                                                    [Reports]:

                                                                                    NoConcatenate

                                                                                    LOAD

                                                                                        ExtractedValue,

                                                                                        "Compliance Score (%)" as "Compliance Score"

                                                                                        ,subfield(Right(filename(),11),'.',1) as Month

                                                                                    FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

                                                                                     

                                                                                     

                                                                                    NEXT;

                                                                                     

                                                                                     

                                                                                    for i = NoOfTables() - 1 to 0 step - 1

                                                                                        let vTable = tablename($(i));

                                                                                    IF WildMatch('$(vTable)','Report*') then

                                                                                     

                                                                                     

                                                                                    Set vMonth= date(date#(num(KEEPCHAR('$(vTable)','0123456789')),'YYYYMM'),'MMM-YY');

                                                                                     

                                                                                     

                                                                                     

                                                                                     

                                                                                    [Asset]:

                                                                                    Load *,

                                                                                    //  $(vMonth) as "Month1",

                                                                                      

                                                                                       alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                                                       Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

                                                                                      

                                                                                       alt(Lookup('Month1','ExtractedValue', HostNameFinal, '$(vTable)'),

                                                                                     

                                                                                     

                                                                                    Lookup('Month1','ExtractedValue', ClusterName, '$(vTable)')) as Month;

                                                                                     

                                                                                     

                                                                                      //Right('$(vTable)',6) as Month

                                                                                     

                                                                                    LOAD

                                                                                      "HostNameFinal",

                                                                                      "ClusterName"

                                                                                    FROM [lib://Qlik Loop/Asset List.xlsx](ooxml, embedded labels, table is Asset);

                                                                                    drop table '$(vTable)';

                                                                                     

                                                                                     

                                                                                    endif

                                                                                     

                                                                                     

                                                                                    next i;

                                                                                     

                                                                                     

                                                                                    drop table dummy;

                                                                                      • Re: Using Applymap to multiple excel files
                                                                                        pradosh thakur

                                                                                        Hi Stanley

                                                                                         

                                                                                        you have to tweak your code a little to get the exact thing you wanted. I cant see your file name neither i have seen your data. You have to try from your side too if you want to get the answers.

                                                                                         

                                                                                        please upload the three files with dummy data may be if you want me to give the exact code. Whatever i can give right now is just a guess as i cant tell how your file name looks like ....

                                                                                         

                                                                                        regards

                                                                                        Pradosh