Help with a calculated field!!!

[jamunmiks]

I am new to Qlik and come from an Excel/Power Query/Power BI background.

I am running a pilot test for a new process and need to be able to categorize the results into "pilot" and "control" groups.

I have two tables: Testers.csv Results.csv

Testers.csv has a two columns, a list of employee ID codes [ID] and the date they first started using the new system [Date.FirstUse]

Results.csv has a variety of columns including [ID] and [Date] which is the date the action was completed.

Basically I want to add a column [Category] that classifies the action as "pilot" if the employee made the decision on or after the date they were trained and default to "control" if the employee made the decision BEFORE the date in [Date.FirstUse] or if the employee is not listed in Testers.csv.

I would need to be able to do this for dozens of different results/sources so adding the column to the actual source data is not practical.

Any help would be appreciated.

[JordyWegman]

Hi Jamun,

Try this:

Testers:
  ID                         as [Testers.ID],
  DateFirstUse               as [Testers.DateFirstUse],
  ID & '|' & DateFirstUse    as %Key
From [Testers] (csv);


Join(Testers):
Load
  ID                         as [Results.ID],
  DateFirstUse               as [Results.DateFirstUse],
  ID & '|' & DateFirstUse    as %Key
From  [Results] (csv);

FinalTable:
Load
  %Key,
  [Testers.ID],
  [Testers.DateFirstUse],
  [Results.ID],
  [Results.DateFirstUse]
  IF([Testers.DateFirstUse] >= [Results.DateFirstUse],'Pilot','Control') as Category
Resident Testers;

Drop table Testers;

Jordy

Climber