Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qvhlaold0
New Contributor III

Column as a flag by prefixing

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

YearCol2Code...
IsInvProj
2010......
0
2011...CGB_15845689
1
2011
CGH_1548666
0
.........
...
2012
CGI_1580125
1
...



Prefixes

YearPrefixProjectsPrefixInvest
2011CGB_CGIP_
2012CGP_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.

Tags (2)
1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: Column as a flag by prefixing

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

4 Replies
jason_michaelid
Honored Contributor II

Re: Column as a flag by prefixing

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

qvhlaold0
New Contributor III

Re: Column as a flag by prefixing

I think that's exactly what I needed.

Many thanks...

O.

Re: Column as a flag by prefixing

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?


talk is cheap, supply exceeds demand
jason_michaelid
Honored Contributor II

Re: Column as a flag by prefixing

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

Community Browser