Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Edit Field Contents

Hi All,

Is it possible to edit the contents of a field one cell at a time via an if-then in Qlikview script? Lets say I have the field 'ApplicantName' and 'ApplicantID'. Because of coding issues, some of the ApplicantIDs are incorrect and need to be manually fixed. What I'd like to be able to do, for now at least, is have a function similar to:

IF (ApplicantName = 'John Smith',222333) AS ApplicantID

but have it replace the value in the field rather than try to write a new one. If I try to run that as is, it gives me an error stating 'Field names must be unique within table'. Any advice?

Thanks!

-Mike

Tags (3)
5 Replies
marcarreras
Valued Contributor

Re: Edit Field Contents

Hi Michael,

In case of a few mistakes in codind, try:

load *, IF (ApplicantName = 'John Smith',222333, ApplicantID) AS ApplicantID_ok   //(extend the IFs conditions as needed)

resident tableorigin;

drop field ApplicantID;

rename field ApplicantID_ok to ApplicantID;

Re: Edit Field Contents

Try this:

IF(ApplicantName = 'John Smith',222333,ApplicantID) AS ApplicantID


Note, you cannot create the field ApplicantID twice in a table, so this will not work:


LOAD

     ApplicantID,

     ApplicantName,

     IF(ApplicantName = 'John Smith',222333,ApplicantID) AS ApplicantID

FROM ....


It will fail because you're trying to create two fields with the same name in a table. That will also happen if you use the * (star) character to load all fields from the source.


talk is cheap, supply exceeds demand
Not applicable

Re: Edit Field Contents

Hi Marc,

This is the approach I've got working for now. However, there's a few too many fixes (around 40) for me to want to just nest the if's. What I'd rather have is each fix as its own IF command so that as we're able to get these things remedied in the source files, I can easily remove them from the QV doc. Is there a way to just replace a single value at a time?

-Mike

Re: Edit Field Contents

Take a look at the applymap function.


talk is cheap, supply exceeds demand
marcarreras
Valued Contributor

Re: Edit Field Contents

Michael, 

Then try to mapp a table with the corrections needed, and then use the applymap function: 


correctiontable: Mapping

LOAD * INLINE [     ApplicantName, ApplicantID_OK    

     John Smith, 222333    

     Jane, 76543 ]; 


tableorigin: LOAD

     ApplicantName, ApplicantID,     

     ApplyMap(correctiontable, ApplicantID) as ApplicantID_OK

FROM ...;



Marc.

Community Browser