topic Re: How to get this? in QlikView

How to get this?

subbareddykm — Wed, 06 Jan 2016 09:31:02 GMT

Hi All,

I am facing one issue with one logic :

Logic is :

If ID= Daily Allowance,Daily Deduction in Expense. I want (Daily Allowance - Daily Deduction) and Store this In Daily Allowance and remove Daily Deduction for Every ID.

How to solve this.Caan anybody tell me solution for this.

For ref Find below attched file

Re: How to get this?

Peter_Cammaert — Wed, 06 Jan 2016 09:55:13 GMT

Daily Deduction already seems to be a negative value. Do you really want Daily Allowance - Daily Deduction?

If not, this script will perform as you want:

RawData:

LOAD ID,

Expense,

Amount

FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1);

GroupedRecs:

NOCONCATENATE

LOAD ID, Sum(Amount) AS Amount2,

'Daily Allowance' AS Expense2

RESIDENT RawData

GROUP BY ID;

Result:

See attachment.

Best,

Peter

Re: How to get this?

PrashantSangle — Wed, 06 Jan 2016 10:04:36 GMT

Hi,

try like,

use fabs()

Daily Allowance - fabs(Dailly Deduction)

Regards

Re: How to get this?

PrashantSangle — Wed, 06 Jan 2016 10:11:29 GMT

Hi,

try like

RawData:

LOAD ID,

//Expense as Daily_Allowance,

Amount as Daily_Allowance

FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1)

where wildmatch(Expense,'Daily Allowance');

left join

LOAD ID,

//Expense as Daily_Deduction,

Amount as Daily_Deduction

FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1)

where wildmatch(Expense,'Daily Deduction');

Final:

Load ID,

Daily_Allowance - Daily_Deduction as Final_Expense

Resident RawData;

Drop table RawData;

REgards

Re: How to get this?

subbareddykm — Wed, 06 Jan 2016 10:21:09 GMT

If i do

'Daily Allowance' AS Expense2.I can't get my requirement.

I have so many expance type If i ahve only two expenses it will work.

Find my attachment.

Re: How to get this?

subbareddykm — Wed, 06 Jan 2016 10:57:45 GMT

Can any one help tell me how tp do this?

Re: How to get this?

Peter_Cammaert — Wed, 06 Jan 2016 14:14:03 GMT

Ah, you change your requirements and your example data...

The solution is simple: filter the rows that you want to group:

RawData:

LOAD ID,

Expense,

Amount

FROM Issue.xlsx (ooxml, embedded labels, table is Sheet1);

GroupedRecs:

NOCONCATENATE

LOAD ID, Sum(Amount) AS Amount2,

'Daily Allowance' AS Expense2

RESIDENT RawData

WHERE Match(Expense, 'Daily Allowance', 'Daily Deduction')

GROUP BY ID;

And if you want to keep all other Expense types in this table, add them back to your Grouped records by adding this statement at the end:

CONCATENATE (GroupedRecs)

LOAD ID, Expense AS Expense2, Amount AS Amount2

RESIDENT RawData

WHERE NOT Match(Expense, 'Daily Allowance', 'Daily Deduction');

Result:

Now you can drop the original table if you don't need it anymore. It stays put in my attachment because it lets you compare INPUT Data to Results

Best,

Peter

Re: How to get this?

subbareddykm — Wed, 06 Jan 2016 14:57:47 GMT

Thank you so much Peter