Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
jhgty43
Contributor II
Contributor II

Data is coming in a certain way, need to find a way to match a field to another field

Hello, I am having an issue trying to manipulate my data after it comes into QlikView.

My data comes in as follows: 

Snag_90a0913.png

I need to find a way in Qlikview to basically match "BERWQ6729" with the top column to replace "Not Assigned." The reason for this is because I need all line items to have the Organization filled out, if it is available for the same ID #. So, I need it to have ID # 54789, Organization BERWQ6729 and Price 28,364 in 1 line together. This is how my data automatically comes in, so I am trying to not have to edit my data ahead of time so that this can be automatically done in Qlikview. 

I basically need the line with the Price to have everything filled out. Does anyone have any idea on how to do this?

 

Thank you in advance.

1 Solution

Accepted Solutions
buddhabang
Contributor III
Contributor III

For the mapping table, just make sure you pull the data from your original source file, with only the 2 fields required.

ID, Organisation. Sample below  and QVW>

 

YourOriginalData:
load * Inline[
ID, Organisation, Price
54789, Not Assigned, 28394
54782, Not Assigned, 27522
54789, BERWQ6729, NULL
]
;


OrgMap:
Mapping
load ID,
Organisation
Resident YourOriginalData
where Organisation <> 'Not Assigned'
;


Fix:
NoConcatenate
load *,
ApplyMap('OrgMap', ID, 'Not Assigned') as NewOrg
Resident YourOriginalData
;


drop table YourOriginalData;

 

 

View solution in original post

5 Replies
jwjackso
Specialist II
Specialist II

You could use the ApplyMap function.  Read through the data collecting the valid ID/Organization combinations and then ApplyMap when you load all the data.  This assumes a 1 to 1 correlation of ID to Organization.

buddhabang
Contributor III
Contributor III

For the mapping....example below

OrgMap:
Mapping
Load * Inline[
ID, Val
54789, BERWQ6729

]
;


Data:
load * Inline[
ID, Organisation, Price
54789, Not Assigned, 28394
54782, Not Assigned, 27522
54789, BERWQ6729, NULL
]
;



Fix:
NoConcatenate
load *,
ApplyMap('OrgMap', ID, 'Not Assigned') as NewOrg
Resident Data
;


drop table Data;

 

buddhabang
Contributor III
Contributor III

For the mapping table, just make sure you pull the data from your original source file, with only the 2 fields required.

ID, Organisation. Sample below  and QVW>

 

YourOriginalData:
load * Inline[
ID, Organisation, Price
54789, Not Assigned, 28394
54782, Not Assigned, 27522
54789, BERWQ6729, NULL
]
;


OrgMap:
Mapping
load ID,
Organisation
Resident YourOriginalData
where Organisation <> 'Not Assigned'
;


Fix:
NoConcatenate
load *,
ApplyMap('OrgMap', ID, 'Not Assigned') as NewOrg
Resident YourOriginalData
;


drop table YourOriginalData;

 

 

View solution in original post

jhgty43
Contributor II
Contributor II
Author

Thank you!

buddhabang
Contributor III
Contributor III

Pleasure