Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ryan_morgan
Contributor
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
Specialist
Specialist

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.

View solution in original post

4 Replies
vamsee
Specialist
Specialist

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
Contributor
Contributor
Author

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

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
Contributor
Contributor
Author

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