Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears,
I hope you are doing well,
I am now developing a new report for HR Manager and one of the his request that he needs to see all salary changes happen for all employees in certain format.
The data structure like this:
Emp Name | Amount | Start Date |
Jolly | 1500 | 2012-02-21 |
Jolly | 1700 | 2016-10-01 |
Sergo | 3000 | 2013-09-01 |
Sergo | 3250 | 2014-07-15 |
Sergo | 3700 | 2015-05-01 |
Sergo | 4000 | 2016-09-20 |
Sara | 900 | 2015-07-29 |
Sara | 1000 | 2016-02-01 |
Sara | 1200 | 2016-03-01 |
Jon | 1600 | 2016-09-01 |
and I want to present it like this:
Emp Name | Old Amount | New Amount | Last Change |
Jolly | 1500 | 1700 | 2016-10-01 |
Sergo | 3000 | 3250 | 2014-07-15 |
Sergo | 3250 | 3700 | 2015-05-01 |
Sergo | 3700 | 4000 | 2016-09-20 |
Sara | 900 | 1000 | 2016-02-01 |
Sara | 1000 | 1200 | 2016-03-01 |
Jon | 1600 | 0 | 2016-09-01 |
Can anyone tell me how can I make this table.
Appreciate your support,
Thanks,
Hi,
one solution could be:
tabTemp:
LOAD * FROM [https://community.qlik.com/thread/238408] (html, codepage is 1252, embedded labels, table is @1);
tabEmps:
LOAD [Emp Name],
If([Emp Name]=Previous([Emp Name]), Previous(Amount), Amount) as [Old Amount],
If([Emp Name]=Previous([Emp Name]), Amount , 0) as [New Amount],
[Start Date] as [Last Change],
AutoNumber([Start Date],[Emp Name]) as ChangeNo
Resident tabTemp
Order By [Emp Name], [Start Date];
DROP Table tabTemp;
Right Join (tabEmps)
LOAD [Emp Name],
ChangeNo
Resident tabEmps
Where [Emp Name]<>Previous([Emp Name]) or ChangeNo>1
Order By [Emp Name], ChangeNo desc;
DROP Field ChangeNo;
hope this helps
regards
Marco
Hi,
one solution could be:
tabTemp:
LOAD * FROM [https://community.qlik.com/thread/238408] (html, codepage is 1252, embedded labels, table is @1);
tabEmps:
LOAD [Emp Name],
If([Emp Name]=Previous([Emp Name]), Previous(Amount), Amount) as [Old Amount],
If([Emp Name]=Previous([Emp Name]), Amount , 0) as [New Amount],
[Start Date] as [Last Change],
AutoNumber([Start Date],[Emp Name]) as ChangeNo
Resident tabTemp
Order By [Emp Name], [Start Date];
DROP Table tabTemp;
Right Join (tabEmps)
LOAD [Emp Name],
ChangeNo
Resident tabEmps
Where [Emp Name]<>Previous([Emp Name]) or ChangeNo>1
Order By [Emp Name], ChangeNo desc;
DROP Field ChangeNo;
hope this helps
regards
Marco
Could also do this:
Dimension 1 = Emp Name
Expression 1 = above(Amount)
Dimension 2 = Amount
Expression 2 = if(above(Amount),only([Start Date]))
But I usually don't like relying on chart inter-record functions because I don't usually like how they behave when I start making selections. I usually prefer modifying the data structures if I find myself doing chart inter-record functions.
Also, it doesn't give you the Jon line, but the Jon line makes no sense to me - Jon's salary did not go from 1600 to 0 on 2016-09-01. It went from 0 to 1600. But no other employees are showing a transition from 0 to their initial salary. So it just doesn't seem like Jon belongs in the chart.
See attached. Borrowed Marco's example.
Hi Marco,
Thank you very much for your help, it is exactly what I want.
Thanks,
Ibrahim
Hi John,
I fully agree with you and that is my intention to not show Jon's case because they don't have any salary changes and it doesn't make sense to present them in this report.
Thank you for your remarks.
Thanks,
Ibrahim
you're welcome
Glad it helped
regards
Marco