topic Re: Keyword Mapping with Description in QlikView

Keyword Mapping with Description

Anonymous — Thu, 27 Aug 2015 14:02:07 GMT

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

sasiparupudi1 — Thu, 27 Aug 2015 14:05:32 GMT

Provide sample data please

Re: Keyword Mapping with Description

Anonymous — Fri, 28 Aug 2015 04:37:29 GMT

Thank you.

Please find the attachment of the sample data.

Re: Keyword Mapping with Description

settu_periasamy — Fri, 28 Aug 2015 05:34:02 GMT

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

Anonymous — Fri, 28 Aug 2015 08:56:18 GMT

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_periasamy — Fri, 28 Aug 2015 09:57:22 GMT

Hi,

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

Re: Keyword Mapping with Description

Anonymous — Fri, 28 Aug 2015 10:04:27 GMT

Hi Settu, Please find the attachment.

Re: Keyword Mapping with Description

sasiparupudi1 — Fri, 28 Aug 2015 10:21:19 GMT

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

Re: Keyword Mapping with Description

Anonymous — Fri, 28 Aug 2015 10:35:36 GMT

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

Re: Keyword Mapping with Description

Anonymous — Fri, 28 Aug 2015 11:07:11 GMT

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_periasamy — Fri, 28 Aug 2015 14:33:52 GMT

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

sasiparupudi1 — Fri, 28 Aug 2015 18:31:09 GMT

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

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

thx

Sasi

Re: Keyword Mapping with Description

MarcoWedel — Fri, 28 Aug 2015 23:40:07 GMT

Hi,

one solution could be also:

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

MarcoWedel — Mon, 07 Sep 2015 16:37:47 GMT

maybe helpful:

Multi-wildmatch on Text

regards

Marco

Re: Keyword Mapping with Description

ziadm — Mon, 07 Sep 2015 17:44:22 GMT

This is the final result

attached is the QVW file

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;