Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
rash_611
Partner - Contributor III
Partner - Contributor III

Subquery in Qlikview

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.

9 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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

sasiparupudi1
Master III
Master III

You could use interval match

please see the following post

IntervalMatch

jagan
Luminary Alumni
Luminary Alumni

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rash_611
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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
Master II
Master II

Hi

Please use as as shown in attached qvw.

Capture.JPG

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;