topic Modifying field values of a QVD in QlikView

Modifying field values of a QVD

dseelam — Wed, 02 Nov 2016 20:32:57 GMT

I have a QVD for whole 2015 with 10 columns among them there is a field ORDER_TYPE with field values say A,B,C,D,E

now some user came up and asking me to modify all b & c order type as z & g

I don't have source to reload it back, so is there a way that can mass modify all b&c as z&g? in the QVD itself??

Re: Modifying field values of a QVD

maxgro — Wed, 02 Nov 2016 20:39:10 GMT

read the qvd, chenge column order_type and store in a new qvd

load

col1,

col2,

....,

col9,

pick(wildmatch(ORDER_TYPE, 'b', 'c', '*'), 'z', 'g', ORDER_TYPE) as ORDER_TYPE

from

yourqvd.qvd (qvd);

store T into yournewqvd.qvd (qvd);

Re: Modifying field values of a QVD

dseelam — Fri, 04 Nov 2016 20:33:36 GMT

Thank Max,

I have an other doubt what if

I have a QVD for whole 2015 with 10 columns among them there is a field ORDER_TYPE with field values say A,B,C,D,E

now some user came up and asking me to modify all b & c order type as z & g if order number is 1234, & 5678??

thanks in advance

Re: Modifying field values of a QVD

maxgro — Fri, 04 Nov 2016 20:40:04 GMT

if(match(ORDER_NUMBER, 1234, 5678),

pick(wildmatch(ORDER_TYPE, 'b', 'c', '*'), 'z', 'g', ORDER_TYPE),

ORDER_TYPE) as ORDER_TYPE

Re: Modifying field values of a QVD

dseelam — Fri, 04 Nov 2016 21:00:20 GMT

Max ,

I got it what if I have bunch of order_numbers like 1000, where there order types have to be changed

how can I handle them

is there any better way ??

Re: Modifying field values of a QVD

maxgro — Fri, 04 Nov 2016 21:13:46 GMT

when ordernumber is 1234 or 5678

type b --> z

type c --> g

load

ordernumber,

if(match(ordernumber, 1234, 5678),

pick(wildmatch(ordertype, 'b', 'c', '*'), 'z', 'g', ordertype),

ordertype) as ordertype

inline [

ordernumber, ordertype

1234, a

1234, b

1234, c

1234, d

5678, a

5678, b,

5678, c

5678, d

1, a

2, b

3, c

4, d

];

Re: Modifying field values of a QVD

dseelam — Fri, 04 Nov 2016 21:17:55 GMT

If this last inline peace takes cares of 1000 of order numbers but there will be duplicates right ?

if i put that inline in my script throwing errors ??

Re: Modifying field values of a QVD

maxgro — Fri, 04 Nov 2016 21:29:35 GMT

what if I have bunch of order_numbers like 1000, where there order types have to be changed

how can I handle them

it depends

do you have some simple way to identify them?

it could be ordernumber > ... and ordernumber <

or ordernumber starts with ....

if not you have to write in some place the order num you want to check; you can use a table and an exists

// table of order to change (b -->z, c-->g)

load * inline [

ordernumlist

1234

5678

];

load

ordernumber,

if(exists(ordernumlist, ordernumber),

pick(wildmatch(ordertype, 'b', 'c', '*'), 'z', 'g', ordertype),

ordertype) as ordertype

inline [

ordernumber, ordertype

1234, a

1234, b

1234, c

1234, d

5678, a

5678, b,

5678, c

5678, d

1, a

2, b

3, c

4, d

];

drop table Z;

Re: Modifying field values of a QVD

dseelam — Fri, 04 Nov 2016 21:50:12 GMT

attached sample copies can you please explain

on Shipments_HHF_ExFlds_FY2015NEW.qvd as source

Test On Order Type as.qvw

and sample to load as input for order types and order number

CA Standard Sales Order

as AFTER

US DotCom Standard Order AS BEFORE