topic Re: SQL script to Qlikview scripts in QlikView

SQL script to Qlikview scripts

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We need write the below sql scripts in qlikview...requesting to help on this..

Report 1 :

SELECT DISTINCT A7AACD , count (A7AACD)

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP)

GROUP BY A7AACD

Report 2 :

SELECT DISTINCT A7AACD , count (A7AACD)

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP) and

A7BANB IN (SELECT BBBANB from INBBCPP)

GROUP BY A7AACD

Report 3 :

SELECT DISTINCT A7AACD , count (A7AACD)

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP) and

A7BANB NOT IN (SELECT BBBANB from INBBCPP)

GROUP BY A7AACD

Report 4 :

SELECT A.A7AACD, B.BBR2TT , COUNT(*)

FROM DSA7CPP A , INBBCPP

B WHERE A.A7BANB = B.BBBANB AND A.A7AIDT = Current date

GROUP BY A.A7AACD, B.BBR2TT

Report 5:

SELECT DISTINCT A7AACD , count (A7AACD)

FROM DSA7CPP

WHERE A7AIDT = Current Date and A7BANB not IN (SELECT BPBANB from INBPCPP) and a7banb in (select bkbanb from inbkcpp) GROUP BY A7AACD.

Thanks..

Re: SQL script to Qlikview scripts

sunny_talwar — Thu, 02 Jun 2016 09:35:20 GMT

Are you saying that you want to do that manipulations in QlikView instead of SQL, while still pulling the dad from SQL?

Re: SQL script to Qlikview scripts

Kushal_Chawda — Thu, 02 Jun 2016 09:40:04 GMT

I suggest that, run SQL query directly from QlikView and create QVDs, thenn load all QVDs in QlikView, and link them on Field A7AACD

Re: SQL script to Qlikview scripts

nareshthavidishetty — Thu, 02 Jun 2016 09:46:42 GMT

yes,i need to do all these manipulations in qlikview script level.

Re: SQL script to Qlikview scripts

nareshthavidishetty — Thu, 02 Jun 2016 09:48:55 GMT

I have tried the below for Reprt1,but it not working,

Tab1:

LOAD BPBANB as A7BANB ;

SQL SELECT BPBANB

FROM AS400D60.DISPDBF.INBPCPP;

Left Join(Tab1)

Tab2:

LOAD A7BANB ,

Count(DISTINCT A7AACD) as A7AACD ,

A7AIDT ;

SQL SELECT

A7AACD,

A7AIDT,

A7BANB

FROM AS400D60.DISPDBF.DSA7CPP

group by A7BANB,A7AIDT;

Thanks..

Re: SQL script to Qlikview scripts

agni_gold — Thu, 02 Jun 2016 09:59:53 GMT

Hi ,

Have you created qvds for all tables ?

if yes then try like below for report 1

Report1 :

Load A7AACD,A7BANB, count (A7AACD)

From DSA7CPP.qvd (qvd)

where match(A7AIDT,'Current')

Group by A7AACD,A7BANB;

Inner Join

Load BPBANB

From INBPCPP.qvd (qvd);

Re: SQL script to Qlikview scripts

Kushal_Chawda — Thu, 02 Jun 2016 10:14:30 GMT

First Create QVDs for all the Reports like below

Report1:

LOAD *,

'Report1' as Flag;

SELECT DISTINCT A7AACD , count (A7AACD) as Count

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP)

GROUP BY A7AACD;

Store Report1 into ..\Report1.QVD;

Drop Table Report1;

Report2:

LOAD *,

'Report2' as Flag;

SELECT DISTINCT A7AACD , count (A7AACD) as Count

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP) and

A7BANB IN (SELECT BBBANB from INBBCPP)

GROUP BY A7AACD;

Store Report2 into ..\Report2.QVD;

Drop Table Report1;

Report3:

LOAD *,

'Report3' as Flag;

SELECT DISTINCT A7AACD , count (A7AACD) as Count

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP) and

A7BANB NOT IN (SELECT BBBANB from INBBCPP)

GROUP BY A7AACD;

Store Report3 into ..\Report3.QVD;

Drop Table Report3;

Report4:

LOAD *,

'Report4' as Flag;

SELECT A.A7AACD, B.BBR2TT , COUNT(*) as Count

FROM DSA7CPP A , INBBCPP

B WHERE A.A7BANB = B.BBBANB AND A.A7AIDT = Current date

GROUP BY A.A7AACD, B.BBR2TT;

Store Report4 into ..\Report4.QVD;

Drop Table Report4;

Report5:

LOAD *,

'Report5' as Flag;

SELECT DISTINCT A7AACD , count (A7AACD) as Count

FROM DSA7CPP

WHERE A7AIDT = Current Date and A7BANB not IN (SELECT BPBANB from INBPCPP) and a7banb in (select bkbanb from inbkcpp) GROUP BY A7AACD;

Store Report5 into ..\Report5.QVD;

Drop Table Report5;

Now load all the QVDs like below

Data:

LOAD *

FROM ..\Report*.QVD(qvd);

Re: SQL script to Qlikview scripts

nareshthavidishetty — Fri, 03 Jun 2016 11:13:17 GMT

Thanks Kushal it's working..

But for the below script,

Report3:

LOAD *,

'Report3' as Flag;

SELECT DISTINCT A7AACD , count (A7AACD) as Count

FROM DSA7CPP

WHERE A7AIDT = Current date and A7BANB IN (SELECT BPBANB from INBPCPP) and

A7BANB NOT IN (SELECT BBBANB from INBBCPP)

GROUP BY A7AACD;

The data isn't available and the Report3 flag is also not available...i need to show Report3 name chart..