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

[a272070]

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

My data comes in as follows: 

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.

[jwjackso]

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]

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 · Accepted solution]

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;

[a272070]

Thank you!

[buddhabang]

Pleasure