topic Re: Subquery in Qlikview in QlikView

Subquery in Qlikview

rash_611 — Tue, 18 Aug 2015 08:04:43 GMT

Hi Team,

I have 2 tables like

Call Details:

ID ,OpenDate and CloseDate

History:

ID, Age and ActivityDate

i want to get the sum of Age from history table of each ID where ActivityDate<CloseDate

This is possible in subquery. but how do we implement this in QV. Kindly suggest.

Re: Subquery in Qlikview

alexandros17 — Tue, 18 Aug 2015 08:08:29 GMT

You can:

NewTab:

Load ID ,OpenDate and CloseDate Resident CallDetails;

left join

Load ID, Age and ActivityDate Resident History;

Now you have a new tab called NewTab with all the fields you need, just use a where condition as you do in sql

let me know

Re: Subquery in Qlikview

rash_611 — Tue, 18 Aug 2015 08:15:39 GMT

no this will not solve my problem... since History table is having huge data... like 15 records for a single ID but as per the logic i want only 4 records which is within CloseDate

Re: Subquery in Qlikview

alexandros17 — Tue, 18 Aug 2015 08:29:22 GMT

Send me the subquery you should write in sql, I will transform in qlik Language ...

Re: Subquery in Qlikview

sasiparupudi1 — Tue, 18 Aug 2015 08:41:52 GMT

You could use interval match

please see the following post

IntervalMatch

Re: Subquery in Qlikview

jagan — Tue, 18 Aug 2015 10:04:35 GMT

Hi,

Try like this

Data:

Load ID ,OpenDate, CloseDate,

If(ActivityDate<CloseDate, 1,0) AS Flag

Resident CallDetails;

Activity:

Load ID, Age and ActivityDate Resident History;

Now in expression try like this

=Sum({<Flag={1}>} Age)

Hope this helps you.

Regards,

Jagan.

Re: Subquery in Qlikview

jonathandienst — Tue, 18 Aug 2015 10:17:54 GMT

This is a way to do it in QV:

T_Data:

LOAD ID,

Age,

ActivityDate

...;

Left Join

LOAD ID,

OpenDate,

ClaseDate

...;

Data:

NoConcatenate

LOAD *

Resident T_Data

Where

ActivityDate >= OpenDate // if you need this

And

ActivityDate < OpenDate;

DROP Table T_Data;

But you might be better off doing the subquery in SQL and only transferring the valid records.

Re: Subquery in Qlikview

rash_611 — Tue, 18 Aug 2015 10:27:04 GMT

select A.id,sum(B.age) from call_detail A, Activity B

where

A.id = B.id and
B.Activity_date <= A.close

group by A.id

Re: Subquery in Qlikview

alexandros17 — Tue, 18 Aug 2015 10:44:06 GMT

TmpTab:

Load ID, Age, ActivityDate Resident History;

left join

Load ID ,CloseDate resident [Call Details];

Final:

noconcatenate

Load ID, Sum(Age) as AgeSum Resident TmpTab Where ActivityDate <= CloseDate Group by ID;

Drop Table TmpTab;

Should have to work, let me know

Re: Subquery in Qlikview

qlikviewwizard — Tue, 18 Aug 2015 13:17:41 GMT

Please use as as shown in attached qvw.

Call_Details:

LOAD ID,

OpenDate,

CloseDate

FROM

[Join.xlsx]

(ooxml, embedded labels, table is Sheet1);

join (Call_Details)

History:

LOAD ID,

Age,

ActivityDate

FROM

[Join.xlsx]

(ooxml, embedded labels, table is Sheet2);

Data:

//LOAD * ,If(ActivityDate<CloseDate, 1,0) AS Flag;

Data:

Load ID ,OpenDate, CloseDate,

If(ActivityDate<CloseDate, 1,0) AS Flag

Resident Call_Details;

Activity:

Load ID, Age,ActivityDate Resident Call_Details;

drop Table Call_Details;