Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subbareddykm
Creator II
Creator II

How to get this?

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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:

HowToGetThis Part2 thread198516.jpg


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



View solution in original post

7 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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:

HowToGetThis thread198516.jpg

See attachment.


Best,


Peter

PrashantSangle

Hi,

try like,

use fabs()

Daily Allowance - fabs(Dailly Deduction)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
PrashantSangle

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



Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
subbareddykm
Creator II
Creator II
Author

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.

subbareddykm
Creator II
Creator II
Author

Can any one help tell me how tp do this?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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:

HowToGetThis Part2 thread198516.jpg


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



subbareddykm
Creator II
Creator II
Author

Thank you so much Peter