topic Re: Tie Datasets based on Run Number? in QlikView

Tie Datasets based on Run Number?

bhelms — Fri, 27 Jan 2017 21:51:33 GMT

I have been trying to figure this out for a few hours now and am not figuring it out. I'm sure there is some simple way to do it, but haven't gotten it yet. I am trying to create a chart of job streams to know when a complete stream starts and finishes. I don't care necessarily what happens in the middle just when the chain starts and ends so we can compare this on a daily basis. Right now I have been able to separate out the first job in the stream and the last job in the stream and when they start and finish each. What I am having trouble doing is joining them together to complete the picture. There is no fields to join on. If I could come up with a Run Number field, I could join on that plus the combination of the date and job stream name. Basically I need the yellow column below. How could I go about that? A count that starts over based on date and job stream name.

Thanks for the help.

Re: Tie Datasets based on Run Number?

MarcoWedel — Fri, 27 Jan 2017 22:51:20 GMT

Hi,

maybe like this:

LOAD [Job Stream Begin Name],
          [Start Time Begin],
          AutoNumber([Start Time Begin], [Job Stream Begin Name]) as [Run Number]
From YourSource;

hope this helps

regards

Marco

Re: Tie Datasets based on Run Number?

vinieme12 — Sat, 28 Jan 2017 07:17:40 GMT

So Job 1 finished as Job 4??

Re: Tie Datasets based on Run Number?

bhelms — Tue, 31 Jan 2017 15:42:07 GMT

I can't seem to get this to work as expected. The two different streams don't show 1 & 2 as I expect but show different numbers so I cannot join on them.

Re: Tie Datasets based on Run Number?

bhelms — Tue, 31 Jan 2017 16:48:02 GMT

No, we have 4 jobs that run concurrently like the below image. All I care about is the overall stream and when it started and when it finished shown in yellow.

Re: Tie Datasets based on Run Number?

swuehl — Tue, 31 Jan 2017 17:01:50 GMT

Could you post a more detailed sample of your input data?

It's unclear how you want to identify a single stream in your data (which consists of different jobs, right?)

If you say, the jobs run concurrently (in the sample it looks like they are running in sequence), does this mean that multiple streams can be processed in parallel?

I think it should / could be possible to use Autonumber, you just need to identify a combination of fields that identify a common group for the numbering.

Maybe concatenate the two tables into one, using just job stream name and date / time (AKA canonical fields instead of separated start / end), then you can maybe use

AutoNumber(RowNumber, [Job Stream Name] & Date) as [Run Number]