Help with a calculated field

[AdamsDriver]

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.

[Lauri]

Do you basically need to join the two tables on [ID] and compare [Date.FirstUse] to [Date]?  If so:

Data:
Load ID, [Date.FirstUse] 
From Testers.csv;

Left Join
Load ID, [Date]
From Results.csv;

Compare:
Load ID,
If([Date]>=[Date.FirstUse], 'pilot', 'control') as Category;

If you have other columns to include from Results, add them to the Left Join statement and Compare load.