topic Re: Replace value by another in QlikView

Replace value by another

realpixel — Tue, 19 Mar 2013 08:25:43 GMT

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);

Re: Replace value by another

somenathroy — Tue, 19 Mar 2013 08:48:28 GMT

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

Re: Replace value by another

kaushiknsolanki — Tue, 19 Mar 2013 08:58:52 GMT

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

Re: Replace value by another

er_mohit — Tue, 19 Mar 2013 08:59:41 GMT

Try this

001 = cars1 or 0018 = cars18

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

Re: Replace value by another

Tue, 19 Mar 2013 09:09:58 GMT

mohit is correct

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

Re: Replace value by another

hic — Tue, 19 Mar 2013 09:10:54 GMT

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

Re: Replace value by another

MayilVahanan — Tue, 19 Mar 2013 09:23:46 GMT

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);