14 Replies Latest reply: Sep 7, 2015 1:44 PM by Ziad Mohammad RSS

    Keyword Mapping with Description

    shiva ganguri

      Hi Friends,

       

      Keyword and Descriptions are in different tables as below

       

       

       

      I am expecting the output like below: -

       

      Can anyone help me.

        • Re: Keyword Mapping with Description
          Sasidhar Parupudi

          Provide sample data please

            • Re: Keyword Mapping with Description
              shiva ganguri

              Thank you.

              Please find the attachment of the sample data.

                • Re: Keyword Mapping with Description
                  Settu Periyasamy

                  Hi,

                  Check this script & attachment.

                  Table1:
                  LOAD * INLINE [
                       Keyword, Issue, Automation
                      cpu,  CPU Utilization, yes
                      memory,  Memory Utilization, yes
                      disk space,  Disk Space/File System, yes
                  ];
                  Temp_Tab1:
                  Load Concat(chr(39)&chr(42)&Keyword&chr(42)&chr(39),',') as Keyword_New,
                    Concat(chr(39)&Issue&chr(39),',') as Issue_New Resident Table1;
                  
                  
                  Let vKeyword=Peek('Keyword_New');
                  Let vIssue=Peek('Issue_New');
                  
                  
                  DROP Tables Temp_Tab1,Table1;
                  
                  Result:
                  LOAD TicketID, 
                       Description,
                       if(WildMatch(Description,$(vKeyword))>0,1,0) as IsMapped,
                       Purgechar(Pick(WildMatch(Description,$(vKeyword)),$(vKeyword)),'*') as Keyword,
                       Pick(WildMatch(Description,$(vKeyword)),$(vIssue)) as Issue     
                  FROM
                  [QVcomm\Keyword Mapping.xlsx]
                  (ooxml, embedded labels, table is Sheet2);
                  
                    • Re: Keyword Mapping with Description
                      shiva ganguri

                      Thanks Settu.

                       

                      But when i added some more data in the inline table, the keywords are not mapping properly to the Issue.

                      please find the code below.

                       

                      Table1:

                      LOAD * INLINE [

                           Keyword, Issue, Automation

                          cpu,  CPU Utilization, yes

                          memory,  Memory Utilization, yes

                          disk space,  Disk Space/File System, yes

                          processor,     CPU Utilization,    yes

                          mount point,    Unknown,    no

                          process killed,    Unknown,    no

                      ];

                       

                      Temp_Tab1:

                      Load Concat(chr(39)&chr(42)&Keyword&chr(42)&chr(39),',') as Keyword_New,

                           Concat(chr(39)&Issue&chr(39),',') as Issue_New,

                           Concat(chr(39)&Automation&chr(39),',') as Automation_New Resident Table1;

                       

                      Let vKeyword=Peek('Keyword_New');

                      Let vIssue=Peek('Issue_New');

                      Let vAutomation=Peek('Automation_New');

                       

                      DROP Tables Temp_Tab1,Table1;

                       

                      Result:

                      LOAD TicketID,

                           Description,

                           if(WildMatch(Description,$(vKeyword))>0,1,0) as IsMapped,

                           Purgechar(Pick(WildMatch(Description,$(vKeyword)),$(vKeyword)),'*') as Keyword,

                           Pick(WildMatch(Description,$(vKeyword)),$(vIssue)) as Issue,

                           Pick(WildMatch(Description,$(vKeyword)),$(vAutomation)) as Automation

                           Resident Incident;   

                        • Re: Keyword Mapping with Description
                          Settu Periyasamy

                          Hi,

                          is it possible to post your file which is not woking?

                          • Re: Keyword Mapping with Description
                            Sasidhar Parupudi

                            Please try

                            Table1:

                             

                            LOAD * INLINE [

                             

                                 Keyword, Issue, Automation

                             

                                cpu,  CPU Utilization, yes

                             

                                memory,  Memory Utilization, yes

                             

                                disk space,  Disk Space/File System, yes

                             

                                processor,     CPU Utilization,    yes

                             

                                mount point,    Unknown,    no

                             

                                process killed,    Unknown,    no

                             

                            ];

                             

                            Map:

                            mapping LOAD Keyword, '@'&Issue&'_'&Automation&'@'

                            Resident Table1;

                             

                            drop Table Table1;

                             

                            Table2:

                            LOAD TicketID,

                                 Description,

                                 if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'@','@'),'_',1))as keyword,

                                      if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'@','@'),'_',2))as Automation

                            FROM

                            [C:\sasi\qv\Keyword Mapping.csv]

                            (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                              • Re: Keyword Mapping with Description
                                shiva ganguri

                                No It is not working. Keywords and Automation are Wrong.

                                  • Re: Keyword Mapping with Description
                                    shiva ganguri

                                    Hi Settu,

                                     

                                    while concatenating it is getting sorted by default to ascending order.

                                    we need to Change it. if it done, then hope it should come correctly.

                                     

                                    Thank you

                                      • Re: Keyword Mapping with Description
                                        Settu Periyasamy

                                        Yes you are correct.

                                        You can try the below script

                                        Table1:
                                        LOAD RowNo()&Keyword as Keyword_,
                                          RowNo()&Issue as Issue_,
                                          RowNo()&Automation as Automation_;
                                        LOAD * INLINE [
                                             Keyword, Issue, Automation
                                            cpu,  CPU Utilization, yes
                                            memory,  Memory Utilization, yes
                                            disk space,  Disk Space/File System, yes
                                            processor, CPU Utilization, yes
                                            mount point, Unknown, no
                                            process killed, Unknown, no
                                        ];
                                        
                                        
                                        Temp_Tab1:
                                        Load Distinct Concat(chr(39)&chr(42)&Keyword_&chr(42)&chr(39),',') as Keyword_New,
                                          Concat(chr(39)&Issue_&chr(39),',') as Issue_New,
                                          Concat(chr(39)&Automation_&chr(39),',') as Automation_New Resident Table1;
                                        
                                        
                                        Let vKeyword=Purgechar(Peek('Keyword_New'),'1234567890');
                                        Let vIssue=Purgechar(Peek('Issue_New'),'1234567890');
                                        Let vAutomation=Purgechar(Peek('Automation_New'),'1234567890');
                                        
                                        
                                        DROP Tables Temp_Tab1,Table1;
                                        
                                        
                                        Result:
                                        LOAD TicketID, 
                                             Description,
                                             if(WildMatch(Description,$(vKeyword))>0,1,0) as IsMapped,
                                             Purgechar(Pick(WildMatch(Description,$(vKeyword)),$(vKeyword)),'*') as Keyword,
                                             Pick(WildMatch(Description,$(vKeyword)),$(vIssue)) as Issue,
                                             Pick(WildMatch(Description,$(vKeyword)),$(vAutomation)) as Automation 
                                        FROM
                                        [Keyword Mapping.xlsx]
                                        (ooxml, embedded labels, table is Sheet2);
                                        
                                      • Re: Keyword Mapping with Description
                                        Sasidhar Parupudi

                                        Hi

                                         

                                        I think your output got messed up because I used @ sign as a delimeter not thinking that email address might be part of your description. I have changed the delimeter to !!!. Hopefully it should show the correct result. Please give the following script a try

                                         

                                         

                                        Table1:

                                         

                                         

                                         

                                        LOAD * INLINE [

                                         

                                         

                                         

                                             Keyword, Issue, Automation

                                         

                                         

                                         

                                            cpu,  CPU Utilization, yes

                                         

                                         

                                         

                                            memory,  Memory Utilization, yes

                                         

                                         

                                         

                                            disk space,  Disk Space/File System, yes

                                         

                                         

                                         

                                            processor,     CPU Utilization,    yes

                                         

                                         

                                         

                                            mount point,    Unknown,    no

                                         

                                         

                                         

                                            process killed,    Unknown,    no

                                         

                                         

                                         

                                        ];

                                         

                                         

                                         

                                        Map:

                                         

                                        mapping LOAD Keyword, '!!!'&Issue&'_'&Automation&'!!!'

                                         

                                        Resident Table1;

                                         

                                         

                                         

                                        drop Table Table1;

                                         

                                         

                                         

                                        Table2:

                                         

                                        LOAD TicketID,

                                         

                                             Description,

                                         

                                             if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'!!!','!!!'),'_',1))as keyword,

                                         

                                                  if(MapSubString('Map',lower(Description))<> lower(Description),SubField(TextBetween(MapSubString('Map',lower(Description)),'!!!','!!!'),'_',2))as Automation

                                         

                                        FROM

                                         

                                        [C:\sasi\qv\Keyword Mapping.csv]

                                         

                                        (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                         

                                         

                                        thx

                                        Sasi

                            • Re: Keyword Mapping with Description
                              Marco Wedel

                              Hi,

                               

                              one solution could be also:

                               

                              QlikCommunity_Thread_178148_Pic1.JPG

                              QlikCommunity_Thread_178148_Pic2.JPG

                              QlikCommunity_Thread_178148_Pic3.JPG

                               

                              tabKeywords:
                              LOAD Keyword,
                                   Issue,
                                   Automation
                              FROM [https://community.qlik.com/servlet/JiveServlet/download/854119-182345/Keyword%20Mapping.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))))
                              Where Len(Keyword);
                              
                              mapKeywords:
                              Mapping LOAD Keyword,
                                           '@start@'&Keyword&'@end@'
                              Resident tabKeywords;
                              
                              tabTickets:
                              LOAD TicketID,
                                   Description
                              FROM [https://community.qlik.com/servlet/JiveServlet/download/854119-182345/Keyword%20Mapping.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));
                              
                              tabLink:
                              LOAD TicketID,
                                   TextBetween(temp,'@start@','@end@',IterNo()) as Keyword
                              While IterNo()<=SubStringCount(temp,'@start@');
                              LOAD TicketID,
                                   MapSubString('mapKeywords',Lower(Description)) as temp
                              Resident tabTickets;
                              
                              

                               

                               

                              hope this helps

                               

                              regards

                               

                              Marco

                              • Re: Keyword Mapping with Description
                                Ziad Mohammad

                                HI

                                 

                                This is the final result

                                attached is the QVW file

                                Tickets.png

                                 

                                 

                                Keywords:

                                LOAD RowNo() AS KeyID ,* INLINE [

                                Keys

                                CPU

                                MEMORY

                                DISK SPACE

                                ];

                                 

                                 

                                TICKETSFILE:

                                LOAD TicketID,

                                     Description

                                FROM

                                [C:\Users\Lenovo\Downloads\Keyword Mapping.xlsx]

                                (ooxml, embedded labels, table is Sheet2);

                                 

                                 

                                 

                                 

                                ISSUES_MAP:

                                MAPPING LOAD

                                  Keys,

                                  '§' & RowNo() & '¨' AS Symbol

                                RESIDENT

                                  Keywords;

                                 

                                 

                                TICKETS:

                                LOAD TicketID,

                                     Description,

                                     MapSubString( 'ISSUES_MAP', Description ) AS KeywordText,

                                     SubStringCount( MapSubString( 'ISSUES_MAP', Description ) , '§' ) AS NoKeywords,

                                     TextBetween( MapSubString( 'ISSUES_MAP' , Description ) , '§' , '¨' , IterNo() ) AS KeyID

                                     Resident

                                     TICKETSFILE

                                WHILE

                                  IterNo() <= SubStringCount( MapSubString( 'ISSUES_MAP', Description ) , '§' );

                                 

                                 

                                DROP Table TICKETSFILE;