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.
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;
Provide sample data please
Thank you.
Please find the attachment of the sample data.
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);
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;
Hi,
is it possible to post your file which is not woking?
Hi Settu, Please find the attachment.
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);
No It is not working. Keywords and Automation are Wrong.
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