Qlikview Interview Questions and Answers Part2

//Q1. list of labels where artist name is 'Kylie' using left joins(using
TextBetween string function)
Q1_2:
LOAD lngLabelID,strLabelName,strArtistName
resident Master
where strArtistName=TextBetween('<Kylie Minogue>', '<', '>');

drop table Q1_2;

//Q1. list of labels where artist name is 'Kylie' using left joins(using
input box)
Q1_3:
LOAD lngLabelID,strLabelName,strArtistName
resident Master
where strArtistName='$(art_name)'; //art_name is the inputbox variable
drop table Q1_3;

//Q1. list of labels where artist name is 'Kylie' using left joins(using
Left string function)
Q1_4:
LOAD lngLabelID,strLabelName,strArtistName
resident Master
where left(strArtistName,5)='Kylie';

drop  table Master;

//Q1.where artist name should have 'MI' in it

LOAD      lngAlbumID,strAlbumName,strArtistName
resident Master
where strArtistName like '*Mi*' ;

//Q2.where album name has digits in it

LOAD  strAlbumName
resident Master
where keepchar(strAlbumName,'0123456789');

//Q3.where album name has '.' in it

LOAD   strAlbumName
resident Master
where strAlbumName like '*.*' ;


//Q4.where album name has only one '.' in it

LOAD   strAlbumName
resident Master
where subfield(strAlbumName,'.');

drop table Master;


//Q5.where album name has only more than 2 occurences '.' in it

LOAD   strAlbumName
resident Master
where substringcount(strAlbumName,'.')>2;//this enables us to limit the no
of charachters

drop table Master;


//Q6.album names without 'the'

LOAD   strAlbumName
resident Master
where substringcount(lower(strAlbumName),'the')<1;

drop table Master;

//Q1:Make three fields from email field username,domain_name,domain_extension
Contacts:
LOAD Address,
     City,
     Country,
     Email,
     FirstName,
     LastName,
     Message,
     Phone
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\25_feb_2011\Contacts.qvd]
(qvd);

resi_1:
load Email,
TextBetween(Email,'','@') as Username,
TextBetween(Email,'@','.') as Domain_name,
TextBetween(lower(Email),'.','')  as Domain_extension
resident Contacts;

drop table Contacts;

//domain extension wise count of domain name
load Domain_extension,count(Domain_name)
resident resi_1
group by Domain_extension;

//domain name wise count of username
load Domain_name,count(Username)
resident resi_1
group by Domain_name;

//Q2.name of customers having 'a'and 'c' and 'u' in their names


//solution1:
load Customer
resident Master
where Customer like '*a*' and Customer like '*c*' and Customer like '*u*';

drop table Master;

//solution_2:
load Customer
resident Master
where (substringcount(lower(Customer),'a')>=1)
and   (substringcount(lower(Customer),'c')>=1)
and   (substringcount(lower(Customer),'u')>=1);

drop table Master;

//Q3.replace '&' by 'and' , 'Supermarket' by 'Store','Store' by
'Shop','Shop' by 'Market'

Resi_1:
load Customer ,
     replace(Customer,'&',' and ') as Cust1,
     replace(Customer,'Supermarket','Store') as Cust2,
     replace(Customer,'Store','Shop') as Cust3,
     replace(Customer,'Shop','Market') as Cust4
resident Master;

drop table Master;


 <!-- Begin BidVertiser code -->
<SCRIPT LANGUAGE="JavaScript1.1" SRC="http://bdv.bidvertiser.com/BidVertiser.dbm?pid=637728&bid=1590320" type="text/javascript"></SCRIPT>
<noscript><a href="http://www.bidvertiser.com/bdv/BidVertiser/bdv_publisher_toolbar_creator.dbm">toolbar creator</a></noscript>

<!-- End BidVertiser code -->