topic Re: Loop Through Sheets and Dates in QlikView

Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 10:44:27 GMT

Hi,

I have a file that loops through each sheet every 15 seconds, the dataset is nothuge sheets change every 15 seconds without any issues.

I have no user interaction and I always have 3 days of information.

So I need to loop through each sheet for each date

So example

Sheet 1 15/02/2017 15 seconds later......

Sheet 2 15/02/2017 15 seconds later.....

Sheet 3 16/02/2017 15 seconds later.....

Sheet 1 17/02/2017 15 seconds later.....

Sheet 2 17/02/2017 15 seconds later.....

Sheet 3 17/02/2017 15 seconds later.....

Sheet 1 18/02/2017 15 seconds later.....

Sheet 2 18/02/2017 15 seconds later.....

Sheet 3 18/02/2017 15 seconds later.....

Again, I am currently looping through the sheets, but I am not looping through the dates as I want.

I tried using sheet triggers on leave by adding a select in field of the date, but that does not work

Hope you can help.

Regards

Alan

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 10:45:37 GMT

I forgot to mention, once I have completed all sheets with all dates, I need to start the loop again.

Thanks

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 10:46:45 GMT

Hi Alan,

Can you share your app?

The sheet triggers should work, sounds a bit weird that it doesn't.

Are you using a macro to do the looping as someone else also had an issue with sheet triggers not working with a macro, seemed to be a timing issue as far as I could work out

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 10:55:36 GMT

Hi Adam,

I cannot share the App unfortunately,I am not using macros and I want to avoid them.

I created a button that does a select in field and when I click it it changes the date, when I add the same select in field to the sheet trigger, nothing changes

I could create a counter variable, so for every 3 sheet changes the date would change, but again the sheet trigger is not working.

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 10:58:12 GMT

Hi Alan,

Not a problem at all, I appreciate that.

That is really weird, let me have a play and see if I get similar issues

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 11:28:46 GMT

Hi Alan,

This is the best way I could find to do this.... I based mine on a 20second rotation due to simple laziness but you can build off it.

Sheet1 visible property =right(Now(),2) <21

Sheet2 visible property =right(Now(),2)>=21 and right(Now(),2) <41

Sheet3 visible property =right(Now(),2)>=41

variable v_timer which is set to =Minute(now())

Then a trigger on that variable 'on change' to select the date:

=if(date(OrderDate+1) > date(max({1}OrderDate)),date(Min({1}OrderDate)),date(OrderDate+1))

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 11:35:58 GMT

Hi Adam,

The date is not changing, it's looping through the sheets alright, but the trigger on the variable is not changing the dates.

Is it changing for you??

Maybe I have disabled triggers somewhere on the document or AccessPoint??

What do you think

Alan.

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 11:39:45 GMT

So the Date changes if I add it to a text box, but it does not select it in the field

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 11:39:52 GMT

Hi Alan,

Yep its definitely changing for me! Adding it to the sheet trigger didn't work as the sheet wasn't being 'activated' just 'shown'

I've checked it in webview and it doesn't work though, the sheets aren't even cycling so could be another V12 bug 😕

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 11:40:52 GMT

Now() doesn't auto refresh in Ajax/Full Browser mode

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 11:43:07 GMT

Weird... I just changed the variable to second(now()) and its merrily scrolling through

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 12:31:21 GMT

Hi Adam,

It does not work on the desktop version.

But.... it does work through access point with Full Browser. I have used it in Chrome and IE and it works.

I have it working on a variable called vShow, vShow changes every 30 seconds to 1,2 or 3 and back again based on the sheet change.

So I added the trigger on that variable, that changes the Date field, and it's working.

But I need to create a new variable that will only change the Date every 90 seconds. As I want the Date to remain for each 30 second view of the sheet and then change to the next date and continue that.

Trying that now.

Thanks

Alan

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 13:04:22 GMT

Hi Adam,

So another weird thing, the date changed when I use vShow which changes every 30 seconds.

But when I create my new variable vShow90, to show every 90 seconds (And it does change when I display it in a text object), when I add the trigger for that variable it does not change the date.

Both Variables are doing the same thing, so why would 1 work and the other not.

Really frustrating!!

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 13:08:15 GMT

Alan,

That is some weird behaviour, something obviously not OK in desktop!

To do 90 seconds I would add this formula in your variable:

=floor(floor((now() - DayStart(today()))*86400)/90)

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 13:10:07 GMT

I'd be logging it with Qlik I think.... something is obviously not working correctly!

Re: Loop Through Sheets and Dates

rustyfishbones — Thu, 16 Feb 2017 13:21:44 GMT

Now it's working in Desktop!!

Going to check in AccessPoint now!!

Crazy!!

Re: Loop Through Sheets and Dates

adamdavi3s — Thu, 16 Feb 2017 13:25:39 GMT

I love this type of behaviour