SQL script to Qlikview scripts

[nareshthavidishetty]

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..

[sunny_talwar]

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

[Kushal_Chawda]

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

[nareshthavidishetty]

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

[nareshthavidishetty]

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..

[agni_gold]

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);

[Kushal_Chawda]

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);

[nareshthavidishetty]

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..