Modifying field values of a QVD

[dseelam]

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??

[maxgro · Accepted solution]

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

T:

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

[dseelam]

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

[maxgro]

if(match(ORDER_NUMBER, 1234, 5678),

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

     ORDER_TYPE)    as ORDER_TYPE

[dseelam]

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 ??

[maxgro]

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

];

[dseelam]

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 ??

[maxgro]

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)

Z:              

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;

[dseelam]

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