topic A really basic question on selections and missing values in QlikView

A really basic question on selections and missing values

Wed, 09 May 2012 13:12:06 GMT

I'm sure this question has come up before and I'm almost embarrassed for asking it. But it's baffling me.

Suppose I have a table of companies (C1, C2, C3, C4, C5, C6) and a second table, mapping companies to head office.

e.g.

Company Head office

C1 HO1

C2 HO1

C3 HO2

C4 HO1

C4 HO2

Note that a company can map to more than one head office in my example. Also, C5 and C6 have no head office.

If I create a table with two columns, Company and Head office, then I'll get a nice list of the mappings, and there will be a '-' for the missing data against C5 and C6. Now, if I filter on Head office by choosing NOT HO2, then the result will only show me HO1. C5 and C6 will no longer be included because there are no associated records in the HO table.

On one hand this makes sense to me, but on the other, surely filtering NOT HO2 should also include those records for which there is no head office. Hopefully, the example attached might make more sense.

One thing I could do is create a mapping for all the companies without a head office (e.g. add C5 and C6 to the map, but call the head office "NA" or something. This would work, I'm sure, but in my live example, there are close to 100,000 rows of data and I'm reluctant to generate so many extra rows if there's a better way of doing it.

Hope this makes sense.

Thanks

James

Re: A really basic question on selections and missing values

Wed, 09 May 2012 20:05:55 GMT

One way you could change this is a resident load. That way it would use the same entries already stored in memory and map the N/A to each of the companies of your choosing.

Joined:

LOAD * INLINE [

Company

C6 ];

left join

LOAD * INLINE [

Company , HeadOffice

C1,HO1

C2,HO1

C3,HO2

C4,HO1

C4,HO2

];

MapFinal:

LOAD Company,

IF(isNull(HeadOffice), 'N/A',HeadOffice)as HeadOffice,

' ' as Junk

Resident Joined;

Drop table Joined;

Attached is an example of this, even for 100,000 rows it shouldn't take that much longer.

Hope this helps!

-Brandon

A really basic question on selections and missing values

Wed, 09 May 2012 20:11:26 GMT

Great solution, thanks Brandon!