Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ryan_morgan
New Contributor

Add mapped column to already loaded table

Hello,

Hoping for some help with adding a column to an already loaded table.  I have to load the table initially to obtain the values for a subroutine to run.  When the subroutine returns it gives me an additional field to add back to the dataset.

OriginalTable:

load *;

sql select

searchColumn,

otherColumn,

otherColumn1

from dbo.table


Sub mySub (searchColumn, OriginalTable)

\\some processes here that succesfully obtain back a new column of data

ResultTable:

searchColumn,

newColumn

MappingTable:

mapping load searchColumn,

     newColumn

resident ResultTable:

//This part is the area where I'm struggling.  I'm trying to get the newColumn from ResultTable onto OriginalTable as an additional field.

Load *, applymap('MappingTable', 'searchColumn', 'Not available') As newColumn resident OriginalTable;

end sub

I was able to get the end result by simply making a new table which is a combination of OriginalTable and ResultTable (via join) but I'm hoping there is a more efficient method than making an entirely new table and dropping the original.

Thanks for any help you can provide! 

1 Solution

Accepted Solutions
vamsee
Valued Contributor

Re: Add mapped column to already loaded table

Yes you have to drop your result table after you created a mapping load. If not Qlik will try to create a relationship with both the tables as the column names are same.

If you do not want to drop the table for some reason, rename the one or both the columns in the result table.

4 Replies
vamsee
Valued Contributor

Re: Add mapped column to already loaded table

Your mapping table looks right.

Can you please post the error message you get.

Also, no quotes for the key

Applymap('MappingTable', SearchColumn, 'Not Available') as NewColumn

ryan_morgan
New Contributor

Re: Add mapped column to already loaded table

Thanks for the quick reply!

I'm not receiving an error message but I get a strange result after I run it:

If I drop the ResultTable after applying the map then my OriginalTable values are unaffected.

If I DON'T drop the ResultTable after applying the map then I get a synthetic key generated tying my ResultTable to my OriginalTable, and my OriginalTable's data ends up empty.

The end result should be:
OriginalTable:
searchColumn, otherColumn, otherColumn1, newColumn,

origdata,origdata,origdata,newData

Demo Code:

OriginalTable:

Load * Inline [

NT; Token

ICMS; '5NNNN'

ICNS; '5NNNM'

](delimiter is ';');

sub LoadCredentials( vSourceTable, vSourceToken)

TempTokenTable:

Load Distinct

$(vSourceToken) As DistinctToken

resident $(vSourceTable);

Let vRunCount = fieldvaluecount('DistinctToken');

Let i = 0;

for  i to vRunCount step 1

Let vCurToken = Peek('DistinctToken', $(i), 'TempTokenTable');

//api call that generates a result set with Token value to Credential value... below code is just to rebuild the

//output since I can't place the api call and restructure here.

next i;

ResultTable:

mapping Load * Inline [

Token; Credential

'5NNNN'; 'Cred1'

'5NNNM'; 'Cred2'

](delimiter is ';');

Load *, ApplyMap('ResultTable', $(vSourceToken) , 'Not Available') As [Credential] resident OriginalTable;

Drop Table ResultTable;

End Sub

call LoadCredentials('OriginalTable', 'Token');

Thanks again for taking a look at this with me.

vamsee
Valued Contributor

Re: Add mapped column to already loaded table

Yes you have to drop your result table after you created a mapping load. If not Qlik will try to create a relationship with both the tables as the column names are same.

If you do not want to drop the table for some reason, rename the one or both the columns in the result table.

ryan_morgan
New Contributor

Re: Add mapped column to already loaded table

Thank you again for your help!  Renaming was the solution I needed.

Community Browser