Qlikview Interview Questions and Answers Part1

Q1.query which displays month which has the highest no of orders

since straight table supports row limitation v can take count first(as nested agg func is not supported) and then select the max and limit the rows to 1)

LOAD
count(distinct OrderID) as ord_count, month(OrderDate) as mth
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd)
group by month(OrderDate);

Q2. list all employees born in month of june (give name& count)there r no bdys hence no output shows bt it wrks for otr months

resi_1:
LOAD
     EmployeeID as June_bdy
resident employee_dm
where month(BirthDate)='jun';

Q3.display the list of employee where suppid same as emplid

suppid_emplid:
LOAD
     OrderID,EmployeeID
     FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);

inner join

LOAD distinct OrderID,
     ProductID
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Order_details.qvd]
(qvd);

inner join

LOAD
     ProductID,
     SupplierID
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Products.qvd]
(qvd);

inner join

LOAD   SupplierID
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Suppliers.qvd]
(qvd);

resi3:
load SupplierID as supp_id
resident suppid_emplid
where SupplierID=EmployeeID;


Q4.display supplier's name wher units in stock is '0'
to reduce time,code,load we can use where clause in the load itslf

ZeroStock:
LOAD      ProductID,
     SupplierID,
     UnitsInStock

FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Products.qvd]
(qvd)
where UnitsInStock=0;

inner join

LOAD CompanyName,
     SupplierID
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Suppliers.qvd]
(qvd);

Q5.create a bucket(range of counts of products) and the count of
suppliers falling in those category

count_prods:
LOAD SupplierID,count(ProductID),
if(count(ProductID)<=2,'0-2',
        if(count(ProductID)<=6,'3-6',
                if(count(ProductID)<=9,'7-9',
                        if(count(ProductID)<=12,'10-12')

                    )
            )
    ) as buck
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Products.qvd]
(qvd)
group by SupplierID;

resi_count_prods:
LOAD count(SupplierID),buck
resident count_prods
group by buck;

drop table count_prods;

//to show blank buckets we concate data with a blank bucket table to
//force buckets to be seen using inline

concatenate
LOAD * INLINE [
    buck,supp_cnt
    7-9
    10-12
];

drop table count_prods;

// list of labels where artist name is 'Kylie' using left joins

Master:
LOAD dteAlbumReleaseDate,
     lngAlbumID,
     lngGenreID,
     lngLabelID,
     strAlbumName
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\25_feb_2011\Album.qvd]
(qvd);

left join

LOAD lngLabelID,
     strLabelName
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\25_feb_2011\Label.qvd]
(qvd);

inner join

LOAD lngAlbumID,
     lngArtistID
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\25_feb_2011\link.qvd]
(qvd);

inner join

LOAD lngArtistID,
     strArtistName
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\25_feb_2011\Artist.qvd]
(qvd);