29 Replies Latest reply: May 12, 2015 8:04 AM by Andre Toerien RSS

    Scripting error qvds

    Andre Toerien

      Hi

       

      I have been struggling for a while with qvd's

       

      I am receiving this error when running this code, what am I missing here.

       

      I am using the Text function to try to change the data type as I was getting qvd's created for 201413, 201414 etc, I only want a file for every actual Period which runs from 2003 - 01 through 12, then 2014-01 to 12

       

      Script line error:

      FOR vPeriod =  to

       

      Noconcatenate TempPeriod:

      LOAD distinct Text(HPeriod) as Period

      Resident HISTORYAGEING

      order by HPeriod;

       

       

      Noconcatenate TempMinMax:

      LOAD FirstValue(HPeriod) as MinPeriod,

      LastValue(HPeriod) as MaxPeriod

      Resident HISTORYAGEING;

       

       

      LET vPeriodMin = Peek(MinPeriod);

      LET vPeriodMax = Peek(MaxPeriod);

       

       

      FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

       

       

      NoConcatenate

      TempData:

      LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

      STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

       

       

      Drop table TempData;

       

       

      NEXT

        • Re: Scripting error qvds
          Sunny Talwar

          May be your MinPeriod and MaxPeriod are not numeric values. Try may be this:

           

          Noconcatenate TempMinMax:

          LOAD Num(FirstValue(HPeriod)) as MinPeriod,

                    Num(LastValue(HPeriod)) as MaxPeriod

          Resident HISTORYAGEING;

           

          LET vPeriodMin = Peek(MinPeriod);

          LET vPeriodMax = Peek(MaxPeriod);

           

          FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

           

          NoConcatenate

          TempData:

          LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

          STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

           

           

          Drop table TempData;

           

           

          NEXT

          • Re: Scripting error qvds
            Gabriel Oluwaseye

            Hi,

             

            I think the logic need to be different here. I will look to use FOR EACH Loop

              • Re: Scripting error qvds
                Andre Toerien

                Can you give me an example?

                 

                On Thu, May 7, 2015 at 11:03 AM, Gabriel Oluwaseye <qcwebmaster@qlikview.com

                  • Re: Scripting error qvds
                    Gabriel Oluwaseye

                    Hi,

                     

                    Are you using calendar script to build this?

                    My reason for asking is, you can still use For Loop.

                    You just have to have a Data Island of all the dates between Min and Max of your date, then use For Loop.


                    • Re: Scripting error qvds
                      Gabriel Oluwaseye

                      Hi,

                       

                      Please try to understand the script below and implement it.

                       

                      I have tried to include notes where I feel clarification is needed.

                       

                      LET vMin = FLoor(MakeDate(2015,04,20));

                      LET vMax = FLoor(MakeDate(2015,04,30));

                      LET vDiff = vMax - vMin + 1; // Plus 1 here ensure we have to full range of date differerence

                      DateIsland:
                      LOAD
                      $(vMin) + RecNo() -1 AS Date   //Minus 1 here ensures that the date starts from the vMin date in our loop
                      AutoGenerate ($(vDiff));
                      //Load full date range
                      FOR i = 0 TO NoOfRows('DateIsland')
                      LET vQVDDate = Date(Peek('Date',$(i),'DateIsland'),'YYYY-MM-DD');
                      TRACE $(vQVDDate);
                      //Here include your store procedure scripts
                      NEXT i;

                       

                        • Re: Scripting error qvds
                          Andre Toerien

                          Thanks Gabriel

                           

                          I think I understand what your script does, the concern I have is still

                          that I do not have full dates, I only have one date field which is not a

                          fulldate, the field comes through from the database as YYYYMM, that is all

                          I have. This originally created the issue that I had that it created files

                          for non existant months, like month 13, 14, because it was a numeric, so

                          when it got to December 2014 it would create a 201413 after December and

                          just carried on with the months. The date range one you gave is really

                          going to help with later scripting on other models, but for this one with

                          only Financial periods, I am just not getting it

                           

                          I have changed the script to a for each loop. It now creates files, but

                          only for the last month

                           

                          Noconcatenate TempPeriod:

                          LOAD HPeriod as Period

                          Resident HISTORYAGEING

                          order by HPeriod;

                           

                          for each Period in TempPeriod

                           

                           

                          NoConcatenate

                          TempData:

                          LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

                                    ;

                           

                                    STORE TempData INTO HISTORYAGEING_$(vPeriod).qvd (qvd);

                           

                                    DROP TABLE TempData;

                           

                          next

                           

                           

                          On Fri, May 8, 2015 at 12:23 PM, Gabriel Oluwaseye <qcwebmaster@qlikview.com

                    • Re: Scripting error qvds
                      Vincent Ardiet

                      Try with quotes in your peek expression like this : LET vPeriodMin = Peek('MinPeriod');

                        • Re: Scripting error qvds
                          Andre Toerien

                          I tried this now with the inverted commas, I am still getting non existing months created between 201412 and 201501, that would be because the field is numbers.

                          Vincent, I am not sure where your specific suggestion is, is what i did basically right, and I am just missing something small somewhere? I am going to have a relook for now at sunindia's suggestion, and Gabril's as well, think I understand better  what he is getting at as well after spending more time looking at his code

                           

                          Noconcatenate TempPeriod:

                          LOAD distinct Text(HPeriod) as Period

                          Resident HISTORYAGEING

                          order by HPeriod;

                           

                           

                          Noconcatenate TempMinMax:

                          LOAD FirstValue(HPeriod) as MinPeriod,

                          LastValue(HPeriod) as MaxPeriod

                          Resident HISTORYAGEING;

                           

                           

                          LET vPeriodMin = Peek('MinPeriod');

                          LET vPeriodMax = Peek('MaxPeriod');

                           

                           

                          FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

                           

                           

                          NoConcatenate

                          TempData:

                          LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

                          STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

                           

                           

                          Drop table TempData;

                          NEXT

                            • Re: Scripting error qvds
                              Sunny Talwar

                              Lets start this from beginning. What format is HPeriod stored in the database? To me the format of HPeriod may have something to do with all the issues that you are getting.

                              • Re: Scripting error qvds
                                Michael Solomovich

                                The noconcatenate is in a wrong place and not needed.  Try this:

                                 

                                TempPeriod:
                                LOAD distinct Period
                                Resident HISTORYAGEING
                                order by HPeriod;

                                 

                                LET vPeriodMin = Peek('Period',0,'TempPeriod');
                                LET vPeriodMax = Peek('Period',-1, 'TempPeriod');

                                 

                                DROP TABLE TempPeriod;

                                 

                                FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

                                 

                                TempData:
                                LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);
                                STORE TempData into HISTORYAGEING_$(vPeriod).qvd;
                                Drop table TempData;

                                 

                                NEXT

                                 

                                Edit: a few fixes

                                  • Re: Scripting error qvds
                                    Andre Toerien

                                    Thanks Michael

                                     

                                    This is the script I have now, had to put noconcatenate back on the one r it added one row to the original table,the problem now is I have data for 201307 to201408 on the model, I commented out TempPeriod, and it is showing all the Periods, though, when exporting to qvd it only creates 201307 and then 201308, nothing else, that says to me that something may be wrong in the ordering where it gets Peek('Period',-1, 'TempPeriod'), but it is not making sense to me at all why it is doing it

                                     

                                     

                                    TempPeriod:

                                    LOAD distinct Period

                                    Resident HISTORYAGEING

                                    order by HPeriod;

                                     

                                    LET vPeriodMin = Peek('Period',0,'TempPeriod');

                                    LET vPeriodMax = Peek('Period',-1, 'TempPeriod');

                                     

                                    DROP TABLE TempPeriod;

                                     

                                    FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

                                     

                                    TempData:

                                    LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

                                    STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

                                    Drop table TempData;

                                     

                                    NEXT

                                      • Re: Scripting error qvds
                                        Michael Solomovich

                                        Here is where the troubleshooting starts...

                                        1. Check what the variables' values are.

                                        2. If they're incorrect, comment the script after loading the TempPeriod, and check what is in TempPeriod.  You probably need to rename the field there, because it also exists in the other table and it is harder to find what exactly in the TempPeriod table.

                                          • Re: Scripting error qvds
                                            Andre Toerien

                                            The table field has all the correct values, I am aliasing it, in the

                                            original table it is HPeriod, in the TempTable it is Period. It comes

                                            through from SQL as an interger.

                                             

                                            When I check the variable values the min value is 200307 and the maxValue

                                            is 200308 where it should be 200408, not really making sense, as if I order

                                            the values by loadorder they still show from 201307 through 201408. Any

                                            idea why peek -1is wrong if the load order seems to be correct. I also have

                                            a order by in the Load statement

                                             

                                             

                                             

                                            On Fri, May 8, 2015 at 3:55 PM, Michael Solomovich <qcwebmaster@qlikview.com

                                            • Re: Scripting error qvds
                                              Andre Toerien

                                              Hi Michael

                                               

                                              I know what the problem is:

                                               

                                               

                                              I set all these variables in the load

                                               

                                              LET vPeriodMin = Peek('Period',0,'TempPeriod');

                                              LET vPeriodMax = Peek('Period',-1,'TempPeriod');

                                              LET vPeriodMin1 = Peek('Period',1,'TempPeriod');

                                              LET vPeriodMin2 = Peek('Period',2,'TempPeriod');

                                               

                                              when showing the values in a text box I get the following values

                                              201307, 201308, 201408, 201407

                                               

                                              To me this says it has something to do with the load order

                                              but my load says:

                                              TempPeriod:

                                              LOAD distinct HPeriod as Period

                                              Resident HISTORYAGEING

                                              order by HPeriod;

                                               

                                              I can't understand why this would be like this. The obvious answer for me is get the load order right, I just have no clue how, the Load on TempPeriod seems right

                                                • Re: Scripting error qvds
                                                  Michael Solomovich

                                                  I think something is missing in your info...  Maybe the variables are re-defined down the road(?)  Order always worked for me.  Anyway, try to return to min and max, so you don't depend on order:

                                                   

                                                  TempPeriod:

                                                  LOAD distinct

                                                        min(Period) as MinPeriod,

                                                        max(Period) as MaxPeriod

                                                  Resident HISTORYAGEING;

                                                   

                                                  LET vPeriodMin = Peek('MinPeriod');

                                                  LET vPeriodMax = Peek('MaxPeriod');

                                                   

                                                  DROP TABLE TempPeriod;

                                                  ...

                                                    • Re: Scripting error qvds
                                                      Andre Toerien

                                                      I am running this like you suggest, It now creates a file for 201313-201400

                                                      and then continues with 201401 through 201408, how do I get rid of the

                                                      months 201313 to 201400 files.Maybe a for each value would be more of use,

                                                      I just can't get that to work either. The number as stated above comes

                                                      through as an integer from the database

                                                       

                                                      On Mon, May 11, 2015 at 2:06 PM, Michael Solomovich <

                                                        • Re: Scripting error qvds
                                                          Michael Solomovich

                                                          I think there is an easier way.

                                                          TempPeriod:

                                                          LOAD distinct HPeriod as  Period

                                                          Resident HISTORYAGEING;

                                                           

                                                          LET NumOfPeriods = NoOfRows('TempPeriod');

                                                           

                                                          After that use For .. Next loop (from 0 to NumOfPeriods-1), using peek() within a loop to get the Period.  Order doesn't matter at all.  It is something like:

                                                           

                                                          FOR r = 0 to $(VRows)-1

                                                          LET vPeriod=peek('HPeriod', $(r), 'TempPeriod');

                                                          TempData:
                                                          LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);
                                                          STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

                                                          Drop table TempData;

                                                          NEXT

                                                          DROP TABLE TempPeriod

                                                  • Re: Scripting error qvds
                                                    Vincent Ardiet

                                                    you can also try to use the debug mode in order to follow step by step your loop