Wildcard Match via Inner Join?

[#1]

Hi All,

I have the following table of Orders.

Ref               Type          Units         

Order1          Drill               10

Order2          CNC              20

Order3          Drill               15

I also have a table of Complete Production.

Ref                Station         

Order1           31

Order2           41   

I have some rules.

Type               Station

Drill                 31

CNC               41

I need to have one table as below that loads the orders, but sets the units to 0 where a valid entry for a station is found against the order.

So my final table would be

Ref               Type          Units         

Order1          Drill               0

Order2          CNC              0

Order3          Drill               15

I'm trying to use an inner join, with a wildcard match but not having much joy.

Any help gratefully received.

Thanks

Steve

[Oleg_Troyansky · Accepted solution]

You can load 2 Mapping tables from "Complete Product" and "Rules". The first table maps Orders to Stations, and the second table maps Types to Stations.

Then, load your transactions and compare the end results of the two maps:

LOAD

     Ref,

     Type,

     IF( ApplyMap('Map1', Ref) = ApplyMap('Map2', Type), 0,  Units ) as Units

RESIDENT

...

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!  

[#3]

Thanks for the ideas.

Regards

Steve