topic Re: Count new asset and transfers in QlikView

Count new asset and transfers

kstroupe — Mon, 24 Apr 2017 20:51:50 GMT

Hello,

I have a SQL view of Asset history which shows asset_id, emp_id and start_dt. I am making a chart to count # of new assets each month and # of transferred assets each month. I am able to find the # of new assets each month, but I'm having difficulty with knowing if the asset has a transfer.

Asset_id emp_id start_dt

1 1 03/01/2017 = New asset in March 2017

2 2 04/01/2016 = New asset in April 2016

2 3 03/01/2017 = Transferred in March 2017

Right now my Chart is counting (1) New in April 2016 and (2) New in March 2017

I would like to see (1) New in April 2016 and (1) New in March 2017 and (1) Transferred in March 2017

Any help is appreciated.

Thanks in advance

Kim

Re: Count new asset and transfers

maxgro — Mon, 24 Apr 2017 21:22:59 GMT

maybe in the script;

you can read the table ordered by asset and date and add a flag when the asset changes

SET DateFormat='MM/DD/YYYY';

load * inline [

Asset_id, emp_id, start_dt, comment

1 , 1, 03/01/2017, = New asset in March 2017

2 , 2 , 04/01/2016 , = New asset in April 2016

2 , 3 , 03/01/2017 , = Transferred in March 2017

2 , 4 , 03/01/2018 , = Transferred in March 2018

3 , 5 , 03/01/2017 , = Transferred in March 2017

];

Z2:

load

if(Peek('Asset_id')=Asset_id, 0, 1) as FlagNew

Resident Z

order by Asset_id, start_dt;

DROP Table Z;

Re: Count new asset and transfers

kstroupe — Mon, 24 Apr 2017 21:37:47 GMT

Worked Perfectly... Your amazing... Thank you so much for your quick reply.