Column as a flag by prefixing

[Anonymous]

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,

O.

[Jason_Michaelides · Accepted solution]

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,

  1

From Excel....;

Map_PrefixInvest

MAPPING LOAD

  Year&PrefixInvest,

  1

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

[Anonymous]

I think that's exactly what I needed.

Many thanks...

O.

[Gysbert_Wassenaar]

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?

[Jason_Michaelides]

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...