topic Re: Problem with sql code to qlikview code in QlikView

Problem with sql code to qlikview code

Sun, 01 Jun 2014 12:50:14 GMT

I have a sql code, but i need to make something like it in qlikview. I tried different ways, but i stuck, someone got an ideas how can it be done.

SELECT

[ContactId]

,[PolicyEndDate]

,[policyDate]

,[PolicyNo]

,[source]

,[policttype]

into #Candidates

from Policy WITH (nolock)

select

[ContactID]

from #Candidates e

inner join #Candidates s

e.contactid = s.contactid

AND e.policytype = s.policytype

AND e.source = s.source

WHERE

ABS(DATEDIFF(DAY,s.PolicyDate , e.PolicyEndDate)) < 30

AND

(e.Policy <> s.Policy)

GROUP BY

[ContactID]

Re: Problem with sql code to qlikview code

eduardo_sommer — Sun, 01 Jun 2014 13:36:22 GMT

I think the problem is the INTO clause in the first select.

Eduardo

Re: Problem with sql code to qlikview code

Sun, 01 Jun 2014 13:42:32 GMT

I just need to transform some how sql code into qlikview code, maybe i make a mistake typing sql code, but it is not the problem i am searching to solve.

Re: Problem with sql code to qlikview code

eduardo_sommer — Sun, 01 Jun 2014 13:57:36 GMT

If I understood your sql code, your query is doing in two steps what can be done in just one. Use only the second SELECT, with FROM POLICY and INNER JOIN POLICY instead of FROM #CANDIDATES and INNER JOIN #CANDIDATES

I think this wil work

Eduardo

Re: Problem with sql code to qlikview code

Sun, 01 Jun 2014 14:32:31 GMT

The problem is i don't know how can i make somefing like

/////

select

[ContactID]

from #Candidates e

inner join #Candidates s

e.contactid = s.contactid

AND e.policytype = s.policytype

AND e.source = s.source

WHERE

ABS(DATEDIFF(DAY,s.PolicyDate , e.PolicyEndDate)) < 30

AND

(e.Policy <> s.Policy)

/////

in qlikview. I can make sql select, but how can i make this work whight qvd files?

Re: Problem with sql code to qlikview code

eduardo_sommer — Sun, 01 Jun 2014 15:25:08 GMT

No, from QVD files you use a Load command

Eduardo

Re: Problem with sql code to qlikview code

Sun, 01 Jun 2014 15:37:32 GMT

I know that, but i don't know how can i make something similar to this sql script.

Re: Problem with sql code to qlikview code

eduardo_sommer — Sun, 01 Jun 2014 15:52:49 GMT

Try this:

TempTable:

Load

[ContactID],

policytype,

source,

PolicyDate,

PolicyEndDate,

Policy

From QVDFile.qvd (qvd);

join(TempTable)

load [ContactID]

policytye,

source,

PolicyDate as PolicyDateS

Policy as PolicyS

Resident TempTable;

Table:

Load [ContactID]

Resident TempTable

Where Fabs(PolicyDateS - PolicyDateEnd) < 30

and Policy <> PolicyS;

Eduardo

Re: Re: Problem with sql code to qlikview code

Anonymous — Sun, 01 Jun 2014 16:07:48 GMT

Hello.

Try something like this:

Candidates_Temp:
Load
    contactid,
    policytype,
    source,
    PolicyDate,
    PolicyEndDate,
    Policy
From Candidates.qvd (qvd);


Inner join (Candidates_Temp) Load
    contactid,
    policytype,
    source,
    PolicyDate as PolicyDate2,
    PolicyEndDate as PolicyEndDate2,
    Policy as Policy2
From Candidates.qvd (qvd);


Candidates:
Load Distinct
     contactid
Resident Candidates_Temp
Where Fabs(PolicyDate2 - PolicyEndDate) < 30
     and Policy <> Policy2;



Drop Table Candidates_Temp;

Hope it helps.

Re: Problem with sql code to qlikview code

Sun, 01 Jun 2014 17:22:35 GMT

Thank you this way worked

Re: Problem with sql code to qlikview code

Sun, 01 Jun 2014 17:22:51 GMT

Thank you it is working