Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
I think that's exactly what I needed.
Many thanks...
O.
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?
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...