topic Re: Scripting challenge - process a masterdata table in QlikView

Scripting challenge - process a masterdata table

datanibbler — Wed, 24 Jul 2013 11:17:20 GMT

Hi,

I want to build a barchart showing the nr. of male and female employees we had in a certain month and the respective ratio.

So far, that's no problem - I can do it on GUI-level using set analysis - but then I have to do it on scripting level so as to be able to make it into a chart.

I have a masterdata table with one record per employee and of course there's a binary field (1/0) for gender that I can count.

The challenge is this:

=> For every employee, there is a date when he/she joined the company and there is a leaving date - that's always filled. For those still here, there's some future date in there.

<=> I will need to generate the information whether that employee was with the company in a present month - for simplicity, on the 1st of a month.

If I had a way of dynamically (in the script) generating the names of months until the present day, I could just generate one more field for every month in that LOAD statement containing an X if an employee has an entry_date before the 1st of that month and a leaving_date after the 1st - or last, if it can be done - of that month.

Up to now, I cannot think of a way I could generate those months.

Can anybody help me there?

Thanks a lot!

Best regards,

DataNibbler

Re: Scripting challenge - process a masterdata table

datanibbler — Wed, 24 Jul 2013 11:27:52 GMT

Hi,

I have now found a way to do this on GUI level that should also work on scripting level:

- I can generate the numeric value of the present month

- By using the >> Monthstart(TODAY(), -X) << expression and counting down from this value to 0, I can do it.

=> I now have to write a loop and run through that as many times (seven times currently) to generate one new field for every past month of this year.

<=> I would like to be able to do this faster, without the need for (currently 7, max. 12) RESIDENT LOADs...

Re: Scripting challenge - process a masterdata table

kaushiknsolanki — Wed, 24 Jul 2013 11:28:33 GMT

Hi,

You need a interval match function to achieve this.

Following link explains the interval match function.

http://community.qlik.com/docs/DOC-3557

Regards,

Kaushik Solanki

Re: Scripting challenge - process a masterdata table

datanibbler — Wed, 24 Jul 2013 11:35:33 GMT

Hi Kaushik,

I know generally how the interval_match function works. I've used it before - but last time I used it, I defined the intervals myself and I had fixed values to match against those - this time, it is upside-down: I have a large number of intervals - between the entry_date and leaving_date which might be different for every employee - and I would have to decide whether a certain month (or date) this year (which I'd also have to generate dynamically) matches any (or several) of those intervals.

Can you please explain how to do this?

Thanks a lot!

Best regards,

DataNibbler

Re: Scripting challenge - process a masterdata table

kaushiknsolanki — Wed, 24 Jul 2013 11:52:48 GMT

Hi,

But even though the interval is big the interval match will work.

Try this.

Create a Inline MasterCal, where start year can by anything which you want and end date will be today.

And use the interval match function to get the flag for the person active on that day or not.

Regards,

Kaushik Solanki

Re: Scripting challenge - process a masterdata table

datanibbler — Wed, 24 Jul 2013 14:32:16 GMT

Hi Kaushik,

I understand so far and I guess that will work.

But:

In the diagram I have already built using interval_match, I have just one "end" of every interval - the upper limit - as my dimension which is fine for that purpose as I catch only a snapshot anyway.

In the diagram I want to build now, I would have to use both ends:

- If I use the start_date only, I might return people as active at a time when they have actually already left.

- If I use the end_date only, I might return people active in a certain month, but who have joined the company only in the middle of that month.

The second of these options would be pretty accurate already as I don't suppose that more than a handful of people are ever hired within a month. That should be sufficient.

I cannot yet imagine how that will have to look, but I will start by carefully reading that document you linked.

Thanks a lot!

Best regards,

DataNibbler

P.S.: Well, that document didn't help me much. I'm still puzzled by the fact that I will have to use the fct "upside-down":

- In the example, the fact table has a specific value and I load some intervals and match that value with the intervals.

<=> In my scenario, I have the intervals (in that I have a start_date and an end_date for each employee) and I have to match these to discrete

intervals (year-month periods, which will have to be dynamically generated, so I guess I'll have to load them from an external table)..

Well, I'll compare this to the diagram I already have and do some thinking and I guess I will come up with something.

I'll be back here once I have done some experimenting and I can tell you exactly how far I can go and where I fail.

Thanks so far!