topic Re: Combining two functions together in scripting in QlikView

Combining two functions together in scripting

sakshikaul — Thu, 18 Jul 2019 05:13:38 GMT

Dear all Following are the two tables and I want to combine two functionalities in one table so for that how to write the script? Basically I have to combine mid function and if conditions stated below LOAD *, Mid(LABNAME,1,index(LABNAME,'-',1)-1) as Lab_Code, Mid(LABNAME,index(LABNAME,'-',1)+1,index(LABNAME,'-',2)-index(LABNAME,'-',1)-1) as Lab_Location, Mid(LABNAME, index(LABNAME,'-',2)+1, Len(LABNAME)-index(LABNAME,'-',2)) as Lab_Zone; SQL SELECT ANALYTE, CITY, Comment, CorrectiveAction, ExpiredDate, FixedCV, FixedMean, FixedSD, from xyz.dbo.xyz; LOAD *, if(LABID = '166051','S03', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'), 'L51','LPL')) as Lab_Code, if(LABID = '166051','Preet Vihar', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Location, if(LABID = '166051','DNCR', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Zone; SQL SELECT ANALYTE, CITY, Comment, CorrectiveAction, ExpiredDate, FixedCV, FixedMean, FixedSD from xyz.dbo.xyz;

Re: Combining two functions together in scripting

lockematthewp — Thu, 18 Jul 2019 17:30:19 GMT

Just add the 'join' keyword between the tables.

LOAD *, Mid(LABNAME,1,index(LABNAME,'-',1)-1) as Lab_Code, Mid(LABNAME,index(LABNAME,'-',1)+1,index(LABNAME,'-',2)-index(LABNAME,'-',1)-1) as Lab_Location, Mid(LABNAME, index(LABNAME,'-',2)+1, Len(LABNAME)-index(LABNAME,'-',2)) as Lab_Zone; SQL SELECT ANALYTE, CITY, Comment, CorrectiveAction, ExpiredDate, FixedCV, FixedMean, FixedSD, from xyz.dbo.xyz;

join

LOAD *, if(LABID = '166051','S03', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'), 'L51','LPL')) as Lab_Code, if(LABID = '166051','Preet Vihar', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Location, if(LABID = '166051','DNCR', if(Match(LABID,'180128','180130','180132','180138','180140','180142','180144','180148','180150','180152','180154','180156','180163'),'KRL','NRL')) as Lab_Zone; SQL SELECT ANALYTE, CITY, Comment, CorrectiveAction, ExpiredDate, FixedCV, FixedMean, FixedSD from xyz.dbo.xyz;

Re: Combining two functions together in scripting

Brett_Bleess — Tue, 13 Aug 2019 12:43:13 GMT

Did the last poster's comment help you resolve things? If so, please be sure to use the Accept as Solution button to give them credit for the help. If you are still working on things, let us know where you are still stuck.

Below is a link that may help a little as well, there are also hundreds of other posts in this area, so be sure to search there in more detail if this one does not do the trick:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

Regards,
Brett