topic Re: Insert a row to QVD file in QlikView

Insert a row to QVD file

upaliwije — Sun, 13 Jul 2014 07:11:06 GMT

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

Re: Insert a row to QVD file

maxgro — Sun, 13 Jul 2014 07:37:16 GMT

- read qvd

- add some rows with concatenate

- store qvd

Directory;

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

Re: Insert a row to QVD file

upaliwije — Sun, 13 Jul 2014 07:45:15 GMT

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)

Re: Insert a row to QVD file

Sun, 13 Jul 2014 07:47:02 GMT

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;

Re: Insert a row to QVD file

its_anandrjs — Sun, 13 Jul 2014 07:48:03 GMT

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

Re: Insert a row to QVD file

its_anandrjs — Sun, 13 Jul 2014 07:57:40 GMT

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

Re: Insert a row to QVD file

maxgro — Sun, 13 Jul 2014 08:16:28 GMT

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