Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
realpixel
Contributor III
Contributor III

Replace value by another

Hello,

How to replace a number by a name? Like 001 = cars1 or 0018 = cars18. I'm novice, someone have an idea which function use and where I must to place this command in my script?

An another question is how to do a subtraction? I use this command, but no result

Delai_D1:

Load [DATE MOV],

     [DATE ENTREE],

     num(Interval([DATE MOV]-[DATE ENTREE], 'D')) as [Time];

I don't know, it's necessary to create table "Delai_D1" to do a subtraction between DAT MOV and DATE ENTREE?

Under my script.

LOAD [CODE PARC],

     Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV], 

     [Nø ALLOTISSMT],

     [CODE MVT],

     [SENS MVT 1/2/3],

     [TAG DER MVT],

     [EXP IMP TRB],

     [V/P],

     [TYPE ISO],

     [FAMILLE VOL.TYP],

     [CODE TRANs],

     [NUMERO IMMAT],

     [LIEU POS],

     [DATE ETA],

     [DATE ETD],

     [CODE DEST. FINAL],

     [POIDS BRUT],

     [HEURE CREATION MVT],

     [PROFIL UTILISAT. CREATION MVT],

     [NUMERO FACTURE],

     [DATE FACTURE],

     Date(Date#([DATE ENTREE],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ENTREE], 

     [MVT ENTREE],

     POOL

   

FROM

C:\TEST\TEST\test.XLS

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

6 Replies
somenathroy
Creator III
Creator III

For First,

if(Left(<Field_Name>,2)='00','Cars' & Mid(Field_Name,3),Field_Name)

For Second,

Substraction between two dates can be done in expression level with the same func you used.

Regards,

Som

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     You can use the Mapping functions to replace the values by another value.

     Have a look at the below link for more information.

     http://community.qlik.com/message/114591

     http://community.qlik.com/thread/54653

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
er_mohit
Master II
Master II

Try this

001 = cars1 or 0018 = cars18

Pick(Match(Fieldname,'001','0018'),'cars1','cars18')AS NEWFIELD

Not applicable

hi

mohit is correct

Pick(Match(Fieldname,'001','0018'),'cars1','cars18') AS NEWFIELD

hic
Former Employee
Former Employee

If you load two tables, one with your data (including the Number that can be 001 or 0018) and a second which justs contains two fields (Number and Name) that maps the number to the name, then 'Cars1' and 'Cars18' will be correctly linked to all the other fields.

If you want it in the same table, you should make an explicit join between the two tables, or even better, use ApplyMap() (See Blog post about Applymap).

The subtraction can be made just by [DATE MOV]-[DATE ENTREE] as Diff. If this doesn't work, your dates have not been interpreted correctly. (See Blog post about Date interpretation)

HIC

MayilVahanan

HI

Try like this

Load 'Cars' & Num(Num#(Numbers)) As CarNum;

Load * inline

[

Numbers

'0013'

'0014'

'0018'

'00100'

];

Make sure, ur date format is in correct format

//

Load *,num(Interval([DATE MOV]-[DATE ENTREE], 'D')) as Day;

LOAD [CODE PARC],

     Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV],

     [Nø ALLOTISSMT],

     [CODE MVT],

     [SENS MVT 1/2/3],

     [TAG DER MVT],

     [EXP IMP TRB],

     [V/P],

     [TYPE ISO],

     [FAMILLE VOL.TYP],

     [CODE TRANs],

     [NUMERO IMMAT],

     [LIEU POS],

     [DATE ETA],

     [DATE ETD],

     [CODE DEST. FINAL],

     [POIDS BRUT],

     [HEURE CREATION MVT],

     [PROFIL UTILISAT. CREATION MVT],

     [NUMERO FACTURE],

     [DATE FACTURE],

     Date(Date#([DATE ENTREE],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ENTREE],

     [MVT ENTREE],

     POOL

  

FROM

C:\TEST\TEST\test.XLS

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.