topic Re: Edit Record on QVD in QlikView

Edit Record on QVD

Mon, 12 May 2014 01:48:14 GMT

Guys, I need your help..

How to write script for edit record on QVD?

for example:

Update table_a

set Map_2='Other'

where Map_2='Opex'

Thank before.

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 01:59:47 GMT

Hi,

You cannot directly update a QVD instead you need to recreate it again like this

Data:

LOAD

Field1,

Field2,

If(Map_2 = 'Opex', 'Other', Map_2) AS Map_2

FROM QVDFileName.qvd;

STORE Data INTO QVDFileName.qvd;

Hope this helps you.

Regards,

Jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 01:59:57 GMT

Hi Valdi, there is direct way to edit the record in the QVD.

First you have to load the QVD and edit the record withe ApplyMap or LookUp or If condition. I personally use ApplyMap because its relatively very fast.

First create the Mapping Table with your edit field values.

MAP_EDIT:

MAPPING LOAD * INLINE [

Map_2, Map_2_New

Opex, Other

Opex1, Other1

];

QVDNAME:

LOAD A, B, C, ApplyMap('MAP_EDIT', Map_2 , Map_2) AS Map_2

From QVDNAME.qvd (qvd);

STORE QVDNAME into QVDNAME.qvd (qvd) ;

DROP Table QVDNAME ;

Re: Edit Record on QVD

Mon, 12 May 2014 02:24:58 GMT

how to edit record for example:

Data:

LOAD

Field1,

Field2,

If(Map_2='OPEX','OTHER',MAP_2) as MAP_2

from QVDFileName.qvd

store Data into QVDFileName.qvd

and I want to keep data 2013, and just data 2014 has changed?

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 02:42:50 GMT

Hi,

Try like this

Data:

LOAD

Field1,

Field2,

If(Year(DateFieldName) = Year(Today()) AND Map_2 = 'Opex', 'Other', Map_2) AS Map_2

FROM QVDFileName.qvd;

STORE Data INTO QVDFileName.qvd;

Replace DateFieldName with your date field name.

Hope this helps you.

Regards,

Jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 02:52:00 GMT

Can You give me write example Year Date?

Data:

Load

Field1, Field2, If (Year(DatePeriode_Report)=Year(Today())) and Map_2='Opex','Other',Map_2) as Map_2 ?

Record Periode Report='2013011','2013012','2014001','2014002','2014003'

how to write the scrip for YearDate?

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 03:01:18 GMT

Hi,

Can you attach some sample data and expected output? or try this

Load

Field1, Field2, If (Left(DatePeriode_Report, 4)=Year(Today())) and Map_2='Opex','Other',Map_2) as Map_2

FROM QVDFileName.qvd;

STORE Data INTO QVDFileName.qvd;

Regards,

Jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 03:16:49 GMT

Great,

Thank you Mr.Jagan Mohan.

Regards.

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 03:25:08 GMT

Hi,

Please close this thread by clicking Correct and Helpful Answer buttons to the posts. It helps others in finding the answers for similar requirement.

Regards,

Jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 04:12:42 GMT

Mr. Jagan,

I'm sorry, I have the last question. how to write script if

Map_3=('Depreciation','Fixed Assets','GA Exp'),'Other',Map_3) as Map_3? <<-- is it true?

I want to edit 3 differrent record ('Depreciation','Fixed Assets','GA Exp') to 'Other' furthermore Map_3

Thanks before.

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 05:52:40 GMT

Hi,

Try like this

Load

Field1, Field2,

If(Match(Map_3, 'Depreciation','Fixed Assets','GA Exp'),'Other',Map_3) as Map_3

FROM QVDFileName.qvd;

STORE Data INTO QVDFileName.qvd;

Regards,

Jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 06:50:32 GMT

Hi Mr. Jagan,

I try with script:

Table_a:

Load

Cost_Centre, Periode_Report, Amount,

if(match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Match(Map_1='Expense','Other','Map_1') as Map_1,

if(Match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Match(Map_2='OPEX','Other OPEX','Map_2') as Map_2

but when I'm trying to reload, show messagebox 'error expression'

Can u help me for the correct script?

Thanks before.

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 06:59:00 GMT

Hi,

Try this

Table_a:

Load

Cost_Centre, Periode_Report, Amount,

if(match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Match(Map_1, 'Expense'), 'Other',Map_1) as Map_1,

if(Match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Match(Map_2, 'OPEX'), 'Other OPEX',Map_2) as Map_2

Regards,

jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 07:19:22 GMT

Hi Mr. Jagan,

Your script is correct, but the problem is when not match then record is 'Map_1', I want when not match then the record same as before like 'assets',or 'liability' etc.

Re: Edit Record on QVD

jagan — Mon, 12 May 2014 07:33:55 GMT

Hi,

Try this

Table_a:

Load

Cost_Centre, Periode_Report, Amount,

if(match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Map_1 <> 'Expense'), 'Other',Map_1) as Map_1,

if(Match(Periode_Report,'201311','201312','201401','201402','201403')

and Match(Cost_Centre,'S812','S813')

and Match(Map_2, 'OPEX'), 'Other OPEX',Map_2) as Map_2

Regards,

jagan.

Re: Edit Record on QVD

Mon, 12 May 2014 07:48:21 GMT

Hi Mr. Jagan.

it's so amazing, I'm so proud of you (y).

I just wanna say thank u very much. Finally I can finish it and very2 happy.

Regards.