Insert a row to QVD file

[upaliwije]

I have following data in a qvd file

DEPT  DESC                AMOUNT

A         INCOME               1000

A         PAYMENT              500

B         INCOME               2000

B         PAYMENT            1000

C         INCOME               3000

C         PAYMENT            1000

I want to INSERT  a row called RETENTION (INCOME-PAYMENT) in the QVD file

Is it possible ? if so pls let me know ho to do it

[maxgro]

- read qvd

- add some rows with concatenate

- store qvd

Directory;

t:

LOAD DEPT, DESC, AMOUNT

FROM t.qvd (qvd);

Concatenate (t)

load * inline [

DEPT , DESC          ,      AMOUNT

NEW  ,       NEW    ,           1000

NEW  ,       NEW   ,           500

];

store t into t.qvd (qvd);

[upaliwije]

Thanks

I am expecting to insert a new row by the name of RETENTION Where the value should be INCOME-PAYMENT

EG:

under Dept A Retention value should be 500  (1000-500)

[#4 · Accepted solution]

Hello,

You can proceed like this:

Data:

Load DEPT, DESC, AMOUNT

from QvdData.qvd(qvd); //here provide your qvd name

Concatenate(Data)

LOAD DEPT,

MinString('RETENTION') as DESC,

Sum(if(DESC = 'INCOME', 1, if(DESC = 'PAYMENT', -1, 0)) * AMOUNT) as AMOUNT

Resident Data

Group By DEPT;

STORE Data into QvdDataUpdate.qvd;

[its_anandrjs]

Try this way

Source:

LOAD * INLINE [

    DEPT, DESC, AMOUNT

    A, INCOME, 1000

    A, PAYMENT, 500

    B, INCOME, 2000

    B, PAYMENT, 1000

    C, INCOME, 3000

    C, PAYMENT, 1000

];

Income_Payment:

LOAD

DEPT,

Sum( if(DESC='INCOME',AMOUNT)) -

Sum( if(DESC='PAYMENT',AMOUNT))  as Inc_Pay

Resident Source

Group By DEPT;

STORE Income_Payment into Income_Payment.qvd(qvd);

[its_anandrjs]

On of the another way of doing this as you required the RETENTION field in the base table then

Source:

LOAD * INLINE [

    DEPT, DESC, AMOUNT

    A, INCOME, 1000

    A, PAYMENT, 500

    B, INCOME, 2000

    B, PAYMENT, 1000

    C, INCOME, 3000

    C, PAYMENT, 1000

];

Join(Source) //You can use concatenate here also

//Inconme_Payment:

LOAD

DEPT,

'RETENTION' AS DESC,

Sum( if(DESC='INCOME',AMOUNT)) -

Sum( if(DESC='PAYMENT',AMOUNT))  as AMOUNT

Resident Source

Group By DEPT;

STORE Source into Source.qvd(qvd);

[maxgro]

Directory;

t: LOAD DEPT, DESC, AMOUNT FROM t.qvd (qvd);

load DEPT,  'RETENTION' as DESC,

sum(if(DESC='INCOME',AMOUNT)) - sum(if(DESC='PAYMENT',AMOUNT)) as AMOUNT

Resident t

group by DEPT, 'RETENTION';

store t into t.qvd (qvd);