Joining tables field not found

[alandilworth]

Hello, I have two tables that I am trying to join named Pharmavail and SandBoxTable respectively.  When I join these, I am wanting to use a field in the Pharmavail table as a conditional statement for the join.  To be specific, there is a StartDate field in the SandBoxTable and a fillDate field in the Pharmavail table.  I want to make sure that the join happens where fillDate >= StartDate.  My problem is it keeps stating that the fillDate field is not found.  I have try using applymaps with incorrect results. 

My script is below.  Any insight will be greatly appreciated!

[Pharmavail]:
LOAD
[month],
[groupId],
[fillDate],
[rxNum],
[rxNewOrRefill],
[brandGeneric],
[pharmNum],
[pharmName],
[pharmAddress],
[pharmAddress2],
[pharmCity],
[pharmState],
[pharmZip],
[shortZip],
[territory],
[entity],
[physicianId],
[physicianName],
'GD'&NDC AS Pharmavail_NDC,
[labelName],
[quantity],
[paidIngCost],
[paidDisp],
[paidSalesTax],
[copay],
[paidAmt]
WHERE groupId='GALTCSH' OR groupId='GALTCSH2';
SELECT `month`,
groupId,
fillDate,
rxNum,
rxNewOrRefill,
brandGeneric,
pharmNum,
pharmName,
pharmAddress,
pharmAddress2,
pharmCity,
pharmState,
pharmZip,
shortZip,
territory,
entity,
physicianId,
physicianName,
NDC,
labelName,
quantity,
paidIngCost,
paidDisp,
paidSalesTax,
copay,
paidAmt
FROM chiefrxdatabase.Pharmavail;

fillDateMapping:
Mapping LOAD Pharmavail_NDC, fillDate Resident Pharmavail;

INNER JOIN(Pharmavail)
LOAD
[NDC] AS Pharmavail_NDC,
[Product],
[WAC],
[StartDate],
[EndDate]
Where ApplyMap('fillDateMapping', [NDC]) >= StartDate;
SELECT NDC,
Product,
WAC,
StartDate,
EndDate
FROM chiefrxdatabase.SandBoxTable;

[Or]

What you're trying to do won't work in Qlik, as far as I know. You'll have to join the two tables first (on the common fields), then load from the ensuing resident and apply your condition.

TempTable:

Load * from Table1;

JOIN

Load * From Table2;

FinalTable:

NoConcatenate Load * Resident TempTable

Where FillDate >= StartDate;

Drop Table TempTable;

Note that I assumed an inner join, but if you need an outer one, you can do that.

[alandilworth]

Thanks for the response!  Are you aware of another workaround where I can apply the condition in the join statement?  My problem is there are duplicate values for the common field in SandBoxTable that I am using to join.  It’s the date column that makes the rows unique.  Because of this, joining it first without the condition gives me incorrect results since it just matches the common fields.  I need it joined by both the common field with the date as a condition. Applying my condition after this will not work (or will be very difficult) since the table will already be set incorrectly. This is something I can do in MySQL but I’m having a very hard time to get it to work here.

[blunckc1]

Hi @alandilworth 

Try a left join instead of an inner join.

Cheers
Carl

[Or]

Sorry, I'm not aware of any such workaround that wouldn't involve scripting with loops, which would be extremely slow for a large number of records and not recommended.

That said, I'm not sure why the approach suggested wouldn't work in the scenario you're describing - sure, TempTable would have some "nonsense" records where the join generated new rows that shouldn't exist, but those will get filtered out in the FinalTable run and you should only be left with rows where the join fields are matched and the condition has evaluated to true. Perhaps there's something I'm missing, but even if that is the case, I don't have any other ideas, sorry!