Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Keyword and Descriptions are in different tables as below
I am expecting the output like below: -
Can anyone help me.
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);
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
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
thx
Sasi
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
HI
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;