Problem with sql code to qlikview code

[#1]

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

ON

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

[eduardo_sommer]

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

Eduardo

[#3]

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.

[eduardo_sommer]

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

[#5]

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

/////

select

[ContactID]

from #Candidates e

inner join #Candidates s

ON

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

[eduardo_sommer]

No, from QVD files you use a Load command

Eduardo

[#7]

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

[eduardo_sommer · Accepted solution]

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

[Anonymous]

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.

[#10]

Thank you this way worked