Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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;
Thank you!
Pleasure