Subquery in Qlikview

[rash_611]

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.

[alexandros17]

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

[rash_611]

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

[alexandros17]

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

[sasiparupudi1]

You could use interval match

please see the following post

IntervalMatch

[jagan]

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.

[jonathandienst]

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.

[rash_611]

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

where

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

group by A.id

[alexandros17]

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

[qlikviewwizard]

Hi

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;