Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jiwaniakbiar
Contributor II
Contributor II

Calculate Age as on date

I have 3 sql tables

MProgram, MParticipant and MProgramParticipant. I want to add new calculated column ParticipantAgeAttended using ProgramStartDate from MProgram and DateOfBirth from MParticipant.


load
ProgramID,
ProgramCode,
ProgramStartDate;

SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;


LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended;

[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

 

 

1 Solution

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

HI,

Try this:

Table1:
load
ProgramID  as ProgramEventID,
ProgramCode,
ProgramStartDate
;
SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

Left Join(Table1)
LOAD
   *
;
LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
;
[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

Left Join(Table1)

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;

FinalTable:
Load
  ProgramEventID,
  ProgramCode,
  ProgramStartDate,
  ParticipantID,
  FirstName,
  LastName,
  DateOfBirth
  Age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended
Resident from Table1;

Drop table Table1;

Jordy

Climber

Work smarter, not harder

View solution in original post

4 Replies
jiwaniakbiar
Contributor II
Contributor II
Author

Reposting with some edits:

I have 3 sql tables

MProgram, MParticipant and MProgramParticipant. I want to add new calculated column ParticipantAgeAttended using ProgramStartDate from MProgram and DateOfBirth from MParticipant.


load
ProgramID  as ProgramEventID,
ProgramCode,
ProgramStartDate;

SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;


LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended;

[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

 

JordyWegman
Partner - Master
Partner - Master

HI,

Try this:

Table1:
load
ProgramID  as ProgramEventID,
ProgramCode,
ProgramStartDate
;
SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

Left Join(Table1)
LOAD
   *
;
LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
;
[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

Left Join(Table1)

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;

FinalTable:
Load
  ProgramEventID,
  ProgramCode,
  ProgramStartDate,
  ParticipantID,
  FirstName,
  LastName,
  DateOfBirth
  Age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended
Resident from Table1;

Drop table Table1;

Jordy

Climber

Work smarter, not harder
jiwaniakbiar
Contributor II
Contributor II
Author

Thanks. Just to clarify, we are merging all the 3 tables and then creating 4rth table which has calculated field. then dropping the big merged table. right?

JordyWegman
Partner - Master
Partner - Master

Exactly that! Good luck!

Jordy

Climber

Work smarter, not harder