topic Re: Add mapped column to already loaded table in QlikView

Add mapped column to already loaded table

Anonymous — Fri, 09 Mar 2018 16:01:21 GMT

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!

Re: Add mapped column to already loaded table

vamsee — Fri, 09 Mar 2018 18:39:40 GMT

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

Re: Add mapped column to already loaded table

Anonymous — Fri, 09 Mar 2018 19:25:51 GMT

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.

Re: Add mapped column to already loaded table

vamsee — Fri, 09 Mar 2018 20:08:49 GMT

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.

Re: Add mapped column to already loaded table

Anonymous — Fri, 09 Mar 2018 20:11:13 GMT

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