topic Wildcard Match via Inner Join? in QlikView

Wildcard Match via Inner Join?

Mon, 09 Mar 2015 22:25:58 GMT

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

Re: Wildcard Match via Inner Join?

Oleg_Troyansky — Mon, 09 Mar 2015 23:10:12 GMT

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!

Re: Wildcard Match via Inner Join?

Tue, 10 Mar 2015 20:35:16 GMT

Thanks for the ideas.

Regards

Steve