topic Column as a flag by prefixing in QlikView

Column as a flag by prefixing

Anonymous — Thu, 27 Sep 2012 04:31:14 GMT

Hello,

I'm QVbeginner and I would like to fill some field in table as flag.

I have table with many columns, about 1 mil. records about accounts from database. I need to create new column as flag: 0 = the row is'nt project/invest, 1 = the row is project/invest.

I know the investment according to the prefix of a specific column in Main table (col. Code). But prefixes are not fixed, prefixes may change each year, so I need load it from XLS spreadsheet.

Main

Year	Col2	Code	IsInvProj
2010	...	...	0
2011	...	CGB_15845689	1
2011		CGH_1548666	0
...	...	...	...
2012		CGI_1580125	1
...

Prefixes

Year	PrefixProjects	PrefixInvest
2011	CGB_	CGIP_
2012	CGP_	CGI_

Please advice how to effectively perform column, what I need to do steps and mapping. Or a completely different approach? I have only idea with create concat from year and prefix "2011CBG_" and it compare with Year+Code in table Main. But how...

For mapping I can read PrefixProjects and PrefixInvest as two separatelly table.

Thanks,

Re: Column as a flag by prefixing

Jason_Michaelides — Thu, 27 Sep 2012 07:31:52 GMT

I think you already know the answer from what you've said, but maybe just don't know the function! You should use ApplyMap()

Map_PrefixProjects:

MAPPING LOAD

Year&PrefixProjects,

From Excel....;

Map_PrefixInvest

MAPPING LOAD

Year&PrefixInvest,

From Excel...;

Data:

LOAD

YourOtherFields,

ApplyMap('Map_PrefixProjects',Year&SubField(Code,'_',1)&'_',

ApplyMap('Map_PrefixInvest',Year&SubField(Code,'_',1)&'_',0)) AS Flag_Project

FROM....;

Hope this helps,

Jason

Re: Column as a flag by prefixing

Anonymous — Thu, 27 Sep 2012 08:19:26 GMT

I think that's exactly what I needed.

Many thanks...

Re: Column as a flag by prefixing

Gysbert_Wassenaar — Thu, 27 Sep 2012 08:20:42 GMT

Jason,

Is it possible to create only one mapping table by concatenating the two needed parts?

What's the reason you use Subfield instead of Index?

Re: Column as a flag by prefixing

Jason_Michaelides — Thu, 27 Sep 2012 08:25:05 GMT

I'm not sure you can concatenate mapping tables. Give it a go, otherwise you could create a standard data table using CONCATENATE and then build the mapping table from this, then drop the standard table.

Using Index(), or Left(), or similar usually means you have to know how many characters the prefix will be. Using SubField(), the delimiter (underscore in this case) can be anywhere in the string and it will still work. Besides, ApplyMap() and SubField() are two of my favourite functions...