Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gsrchowdary
New Contributor

Keyword Mapping with Description

Hi Friends,

Keyword and Descriptions are in different tables as below

 

I am expecting the output like below: -

Can anyone help me.

1 Solution

Accepted Solutions
ziadm
Valued Contributor

Re: Keyword Mapping with Description

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

(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;

14 Replies
sasiparupudi1
Honored Contributor III

Re: Keyword Mapping with Description

Provide sample data please

gsrchowdary
New Contributor

Re: Keyword Mapping with Description

Thank you.

Please find the attachment of the sample data.

Re: Keyword Mapping with Description

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

gsrchowdary
New Contributor

Re: Keyword Mapping with Description

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

Hi,

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

gsrchowdary
New Contributor

Re: Keyword Mapping with Description

Hi Settu, Please find the attachment.

sasiparupudi1
Honored Contributor III

Re: Keyword Mapping with Description

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

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

gsrchowdary
New Contributor

Re: Keyword Mapping with Description

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

gsrchowdary
New Contributor

Re: Keyword Mapping with Description

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