19 Replies Latest reply: Nov 25, 2009 8:39 AM by William Crendal RSS

    An interesting load question

      I'm trying to load a file where the metrics are grouped by the first column, grouped as showen below

      So for example, I sold Fords on 10/1-10/5. How can my QV Load regonized 10/2/-10/5 as belonging to Ford?

      My result show look like the following

        • An interesting load question

          Hi Jonathan,

          if it is an excel file than:

          go to table viewer, select your table, qlik next until you see "Enable Transformation Step" => "FILL" => Fill Cell.

          and define your rule.

          Good luck!

          Rainer

            • An interesting load question
              Daniel Blank

              Hi Rainer,

              what if it is not an Excel file? I have this case...

              Best regards,

              Daniel

                • An interesting load question

                  You should use the Previous function IF(isnull(Field),previous(Model),Model) as Model

                  But in this case you need to add an Order by in your load statement.

                  Sébastien

                  • An interesting load question
                    Miguel Angel Baeyens de Arce

                    I'd better use "Rotate" / "Transpose" in the "Enable Transformation Step" (QV v9.00 SR1)

                      • An interesting load question
                        Daniel Blank

                        Well, I´m araid, it is not an Excel file.

                        I get account data in the format

                        Account CounterAccount
                        5400 2200
                        2300
                        4300
                        5600 2100
                        3600
                        4200
                        1300
                        4900

                        Now I need to fill the empty values in the field Account. The previous() or peek() function does only work if the previous record has a value for Account and I don´t know how many records back I have to look. Every account can have many CounterAccounts... The number is not fix.

                        Any idea?

                        Kind regards,

                        Daniel

                          • An interesting load question

                            Maybe with a kind of loop on the table...

                            For each value if Model is null then use variable else LET variable = Model...

                            Sébastien

                            • An interesting load question
                              Tom Mackay

                              Daniel, peek() will do what you need. The reason is that peek() pulls from the current associative DB table, while previous() pulls from the data source. This is a subtle difference, and in some cases peek works where previous does not or vice versa. So in your case (assuming the data is in correct order), you can read the first record with an account, then the second record looks at the first and picks up the account, then the third record reads the second record and so on. So you are always only looking back one record at the data that has already been loaded and updated.

                              Something like:

                              IF(isnull(Account), peek('Account'), Account) as Account

                              Tom

                                • An interesting load question
                                  John Witherspoon

                                  I was about to say the same. I couldn't guess why peek() wouldn't work, so I built myself an example, and it worked just fine, as I'd have expected. I'd avoid IsNull() because of issues with it on 64-bit, and you don't need the single quotes. I honestly thought the single quotes would prevent it from working, but they apparently don't. Anyway, the example I'd built uses this script (in the real world, make sure your data is sorted properly):

                                  LOAD
                                  if(len(Account),Account,peek(Account)) as Account
                                  ,CounterAccount
                                  INLINE [
                                  Account,CounterAccount
                                  5400,2200
                                  ,2300
                                  ,4300
                                  5600,2100
                                  ,3600
                                  ,4200
                                  ,1300
                                  ,4900
                                  ];

                                    • An interesting load question
                                      Tom Mackay

                                      The full syntax is with the single quotes, but as you've observed it seems to work without them as well.

                                        • An interesting load question
                                          Daniel Blank

                                          Funny thing - it works with the Inline command.

                                          I tried this with a sample code reading a sample excel document... That doesn´t work.

                                          What is wrong with my code?

                                          Regards,

                                          Daniel

                                          • An interesting load question
                                            Rob Wunderlich

                                             


                                            Tom Mackay wrote:The full syntax is with the single quotes, but as you've observed it seems to work without them as well.<div></div>


                                            Leaving off the quotes only works in the simplest cases and I would recommend following the doc and always including the quotes. See

                                            http://community.qlik.com/wikis/qlikview-wiki/forgetting-quotes-in-peek.aspx

                                            for examples of problems caused by leaving off the quotes.

                                            -Rob

                                              • An interesting load question
                                                John Witherspoon

                                                Yeah, I realized I was playing with fire once I looked it up, and it did indeed have the single quotes. I'll definitely try to remember that in the future, and I should probably check a few applications to make sure I'm doing it right.

                                                • An interesting load question
                                                  Tom Mackay

                                                  You're preaching to the choir Rob. I was going to put a more verbose answer in along the same lines myself.

                                                    • An interesting load question
                                                      Rob Wunderlich

                                                       


                                                      Tom Mackay wrote:You're preaching to the choir Rob


                                                      Everybody sing...

                                                      I wonder if we could get Qliktech to consider making this a fatal syntax error? It works without quotes sometimes and other times fails silently (returns a null) depending on where you are in the script.

                                                      I'm willing to submit a feature request along the lines of:
                                                      "Fail script with a syntax error if quotes are missing around peek() arguments".

                                                      Or is there a valid application for using an unquoted string? Am I missing something?

                                                      This would be a breaking change for apps that are running without the quotes.

                                                      -Rob

                                                        • An interesting load question
                                                          John Witherspoon

                                                          We're probably drifting further and further off topic, but I would want it to behave differently.

                                                          While I could easily be wrong, I suspect that the current syntax is a result of lazy coding rather than one of intentional design. I say this because I've done almost this exact same thing. A couple years ago, I added an exists() function to a custom compiler/interpreter at our company. The function is intended to tell you whether a field exists in the current data set or not. However, fields in a function are always evaluated before the function is executed on them. If we try to evaluate a field that does NOT exist, the interpreter gives a run time error. Under time pressure, it was much easier to just pass in the field name as a literal instead of as a field name, thus avoiding the run time error. Literals in this compiler are in double quotes, so my syntax was exists("field") instead of the desired exists(field).

                                                          So it would not surprise me if peek('field'), applymap('mapping table',field) and the like are simply the result of time pressures to put out new features rather than intentional decisions to deviate from the norm. Perhaps, as in my case, reading in the field or table names as literals was a kluge to avoid more drastic overhauls to the core code of the system.

                                                          So what I would want in a feature request isn't a syntax error, but rather consistencey - literals in single quotes, field names in double quotes or brackets (or neither if no white space), search strings in double quotes, and so on. I think it is poor form to require putting a field name or table name in single quotes for occasional functions. I don't like seeing what I consider violations of a standard. It detracts from ease of use and so on.

                                      • An interesting load question
                                        William Crendal

                                        In your load script, add a field for the previous record value of the first column (when it is not the first record), then check the value of the first column, if blank then use the previous record value.