31 Replies Latest reply: Jun 22, 2011 10:27 AM by At titude RSS

    Variable to get the max(date)

      Hi

       

      After loading the data in the Login table to how can I get the Max(LoginDate) and store it in a variable which has to be used while appending the only the new data into the QVD.

       

      Login:
      
      LOAD @1 as LoginDate, 
           @2 as User, 
           @3 as Product
      FROM
      [C:\status.txt]
      (txt, codepage is 1252, no labels, delimiter is ',', msq);
      
      //Help required to create the variable. Which will be used in the below script to append only those data which 
      //is not there in the above table records
      
      Store * from Login into Login.qvd;
      
      CONCATENATE
       
      LOAD @1 as LoginDate, 
           @2 as User, 
           @3 as Product
      FROM
      [C:\status.txt]
      (txt, codepage is 1252, no labels, delimiter is ',', msq)
      WHERE LoginDate > Max(LoginDate) which will come from the variable. I need help for this please.
      ;
      
      Store * from Login into Login.qvd;
      
      

       

      Thanks

       

      Attitude

        • Variable to get the max(date)

          Attitude,

           

          If i understood your question Properly.I`m sure we dont need to store the value in variable to take the max of valuee from you login table. Your code should look like below.

           

          Login:

           

          LOAD @1 as LoginDate,

               @2 as User,

               @3 as Product

          FROM

          [C:\status.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

          //Help required to create the variable. Which will be used in the below script to append only those data which

          //is not there in the above table records

           

          Store * from Login into Login.qvd;

           

          /////////////////////////EDITED PATRT OF THE SCRIPT////////////////////////////////

           

          NEW_LOGIN_TABLE:

           

          NOCONCATENATE

           

          LOAD DATE(MAX(LoginDate)) AS LoginDate

          RESIDENT Login;

           

          INNERT JOIN

           

          LOAD @1 as LoginDate,

               @2 as User,

               @3 as Product

          FROM

          [C:\status.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

          CONCATENATE (Login)

           

          LOAD * RESIDENT NEW_LOGIN_TABLE;

           

          DROP TBALE NEW_LOGIN_TABLE;

           

          /////////////////////////////////////////////////////////////////////////////////////

           

          Store * from Login into Login.qvd;

           

           

          Hope this helps you.

           

          - Sridhar

            • Re: Variable to get the max(date)

              Hi Sridhar

               

              Thanks for your help! I didn't try the solution that has been given by you as I didn't understood.

               

              I have done something like below. Only problem in the below code is I am not checking max(LoginDate) or ReloadTime() while concatanting the 2nd text file. I am concatanating all the data from the 2nd text file into the qvd instead of loading only those data which is not there in the existing qvd.

               

               

              Login:
              LOAD @1 as LoginDate, 
                   @2 as User, 
                   @3 as Product
                   //,
                   //ReloadTime() as LastReload
              FROM
              [C:\status.txt]
              (txt, codepage is 1252, no labels, delimiter is ',', msq);
              Store Logon into Login.qvd (qvd);
              Drop table Login;
              Login:
              LOAD LoginDate, 
                   User 
                   Product
              FROM
              [C:\Logon.qvd]
              (qvd);
              CONCATENATE
              LOAD @1 as LoginDate, 
                   @2 as User, 
                   @3 as Product
              FROM
              [C:\status.txt]
              (txt, codepage is 1252, no labels, delimiter is ',', msq)
              ;
              Store Login into Login.qvd (qvd);
              
              

               

              Hope it is understandable about my requirement. If no please let me know!

               

               

               

              Thanks

               

              Attitude

                • Re: Variable to get the max(date)

                  Hi Sridhar

                   

                  I think what I did may not be the right way of doing it. Instead I got one more idea. I think we use the below script which is between the block(/////) in a seperate application so that this script does not executed again and again.

                   

                  There is one more way of doing it. I think I need to search for some kind of function which check whether qvd is available or not. If the qvd is already available script between the block(//////) should not get executed. What do you say?

                   

                  //////////////////////////////////////////////////////////////////
                  Login:
                  
                  LOAD @1 as LoginDate, 
                       @2 as User, 
                       @3 as Product
                       //,
                       //ReloadTime() as LastReload
                  FROM
                  [C:\status.txt]
                  (txt, codepage is 1252, no labels, delimiter is ',', msq);
                  Store Logon into Login.qvd (qvd);
                  
                  Drop table Login;
                  ///////////////////////////////////////////////////////////////////
                  Login:
                  
                  LOAD LoginDate, 
                       User 
                       Product
                  FROM
                  [C:\Logon.qvd]
                  (qvd);
                  
                  CONCATENATE
                  
                  LOAD @1 as LoginDate, 
                       @2 as User, 
                       @3 as Product
                  FROM
                  [C:\status.txt]
                  (txt, codepage is 1252, no labels, delimiter is ',', msq)
                  ;
                  
                  Store Login into Login.qvd (qvd);
                  
                  
                • Re: Variable to get the max(date)

                  Hi

                   

                  I didn't try with the solution that you have provided as I need to understand everything before implementing in mine. I am trying something like below. If I am loading it for the first time as there won't be any Login.qvd created. Could which is inside the block(/////) will be executed. For next time onwards whatever the code which is there after "end if" will be executed(Which will concatenate the data into the login.qvd from the text file and as well from the session log).

                   

                  One which I still wanted to do it here is I want to check the max(LoginDate) which is loaded from the text file and max(LoginDate) which is loaded from the sessions qvd. Both is there now in a single qvd(Login.qvd). Using this two max(LoginDate) I want to concatenate the data in to the Login.qvd which is there in the 2nd block.

                   

                  Please guide me on how to to get the max(LoginDate) from Text file and Session log which can be used in the 2nd block. I think to get the Max(LoginDate) we need to write where condition to pull the Max(LoginDate) from the one which is loaded in the text file and Sessions Log.

                   

                   

                  /**************************************** 1st Block*************************************/
                  //////////////////////
                  
                  if isnull(filetime('C:\Login.qvd')) then
                  
                  Login:
                  
                  LOAD @1 as LoginDate, 
                       @2 as User, 
                       @3 as Product
                  FROM
                  [C:\Status.txt]
                  (txt, codepage is 1252, no labels, delimiter is ',', msq);
                  
                  Concatenate
                  
                  LOAD Product, 
                       LoginDate,
                       User
                  FROM
                  $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                  Store Login into Login.qvd (qvd);
                  
                  Drop table Logon;
                  
                  end if;
                  ////////////////////////
                  
                  /**************************************** 2nd Block*************************************/
                  Logon:
                  
                  LOAD LoginDate, 
                       User, 
                       Product
                  FROM
                  [C:\Documents and Settings\ChanRi\Desktop\SRS.MRS Work\Logon.qvd]
                  (qvd);
                  
                  CONCATENATE
                  
                  LOAD @1 as LoginDate, 
                       @2 as User, 
                       @3 as Product
                  FROM
                  [C:\Status.txt]
                  (txt, codepage is 1252, no labels, delimiter is ',', msq)
                  ;
                  
                  CONCATENATE
                  
                  LOAD Product, 
                       LoginDate,
                       User
                  FROM
                  $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                  
                  Store Logon into Logon.qvd (qvd); 
                  
                  • Re: Variable to get the max(date)

                    Hi

                     

                    Deepak suggested you to him me on this. Please try to help me out when you find free time.

                     

                    I want to load only those data which is not available in the WHERE condition of the 2nd block. I tried to store the max(logindate) of both text file and session log in a varialble but it didn't work. Also tried to load the max(LoginDate) from both the text file and sessions log in a seperate table of a field but it didn't work either.

                     

                    Can some please do the required change in the 2nd block of my code so that it store only those rows which is not available in the existing qvd(I need your help where the text is in BOLD letters in 2nd block). This is urgent requirement! Hope you can understand!

                     

                     

                    /**************************************** 1st Block*************************************/
                    //////////////////////
                    if isnull(filetime('C:\Login.qvd')) then
                    
                    Login:
                    
                    LOAD @1 as LoginDate, 
                         @2 as User, 
                         @3 as Product
                    FROM
                    [C:\Status.txt]
                    (txt, codepage is 1252, no labels, delimiter is ',', msq);
                    
                    Concatenate
                    
                    LOAD Product, 
                         LoginDate,
                         User
                    FROM
                    $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                    Store Login into Login.qvd (qvd);
                    
                    Drop table Logon;
                    
                    end if;
                    ////////////////////////
                    /**************************************** 2nd Block*************************************/
                    Logon:
                    
                    LOAD LoginDate, 
                         User, 
                         Product
                    FROM
                    [C:\Documents and Settings\ChanRi\Desktop\SRS.MRS Work\Logon.qvd]
                    (qvd);
                    
                    CONCATENATE
                    
                    LOAD @1 as LoginDate, 
                         @2 as User, 
                         @3 as Product
                    FROM
                    [C:\Status.txt]
                    (txt, codepage is 1252, no labels, delimiter is ',', msq)
                    WHERE Date(@1) > Max(LoginDate) of text file;
                      
                    CONCATENATE
                    
                    LOAD Product, 
                         LoginDate,
                         User
                    FROM
                    $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
                    WHERE Date(@1) > Max(LoginDate) of session log;
                    
                    Store Logon into Logon.qvd (qvd); 
                    
                    

                     

                    Thanks

                     

                    Attitude

                      • Re: Variable to get the max(date)

                        Hi,

                         

                        I have modified your 2nd block below. what i have understood form your earlier post is , You want to load the max of logindate from Logon tabel and load the respective data from the session and status table. if that is your problem then below is the code for your problem.

                         

                        What I’m doing here is creating the temp table to store max value of the logon date and and storing the same to a variable and using that variable in session &status Table to restrict data.

                         

                         

                        Logon:

                         

                        LOAD LoginDate,

                             User,

                             Product

                        FROM

                        [C:\Documents and Settings\ChanRi\Desktop\SRS.MRS Work\Logon.qvd]

                        (qvd);

                         

                         

                        Logon_Temp:

                        Load Max(LoginDate) as LoginDate_Max                        //Taking the max of date from Login table

                        Resident Logon;

                         

                        Let vStoreMaxLogin = Peek(LoginDate_Max,-1, Logon_Temp);   //storing the max date in variable

                         

                        Drop table Logon_Temp; //Droping the Temp table, Since we are not going to use this in any of  our below

                         

                        CONCATENATE (Logon)

                         

                        LOAD @1 as LoginDate,

                             @2 as User,

                             @3 as Product

                        FROM

                        [C:\Status.txt]

                        (txt, codepage is 1252, no labels, delimiter is ',', msq)

                        WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file;  // here vStoreMaxLogin is the

                        //variable which stores the max of login date. Make //sure your date format is correct.

                         

                        CONCATENATE (Logon)

                         

                        LOAD Product,

                             LoginDate,

                             User

                        FROM

                        $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)

                        WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of session log; // here vStoreMaxLogin is the

                        //variable which stores the max of login date. //Make sure your date format is correct.

                         

                        Store Logon into Logon.qvd (qvd);

                         

                         

                        I`m attaching a sample application in which i have shown, how to take the max of value to a variable using peek() function.

                         

                        Have a look at the attached application also.

                         

                         

                        Hope this may help you.

                         

                         

                        - Sridhar

                         

                        Edit : Attached a application to demonstarte the max value to store in variable.

                          • Re: Variable to get the max(date)

                            Hi Sridhar

                             

                            Thanks alot for your help. I am not sure why peek function is required to find the max(LoginDate). Anyway that is not my question now.

                             

                            Now my question is when I ran the script after doing the required change as per your suggestion it says that "vStoreMaxLogin" field is not found when the debugger comes to this location. Please suggest something here for me! Let me know what changes are required here. I did exactly the same changes that you have suggested here.

                             

                            CONCATENATE (Logon)
                              
                            LOAD @1 as LoginDate, 
                                 @2 as User, 
                                 @3 as Product
                            FROM
                            [C:\Status.txt]
                            (txt, codepage is 1252, no labels, delimiter is ',', msq)
                            WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file; 
                            
                            


                            Thanks

                             

                            Attitude

                              • Re: Variable to get the max(date)

                                Hi Sridhar

                                 

                                I think the problem could be somewhere here(Highligted in bold). Yesterday I tried to pass the variable in it like the way you have passed but didn't work. Thats where the confusion is. Please help me out when you find free time as you must be very busy with your work.

                                CONCATENATE (Logon)
                                  
                                LOAD @1 as LoginDate, 
                                     @2 as User, 
                                     @3 as Product
                                FROM
                                [C:\Status.txt]
                                (txt, codepage is 1252, no labels, delimiter is ',', msq)
                                WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file; 
                                
                                
                                
                                  • Re: Variable to get the max(date)
                                    Miguel Angel Baeyens de Arce

                                    Hi,

                                     

                                    You need to expand the variable in the WHERE clause

                                     

                                    WHERE Date(@1) > Max(Date#($(vStoreMaxLogin),’DD/MM/YYYY’));
                                    

                                     

                                    Hope that helps.

                                     

                                    Miguel Angel Baeyens

                                    BI Consultant

                                    Comex Grupo Ibérica

                                      • Re: Variable to get the max(date)

                                        Hi Miguel

                                         

                                        I have also thought of samething and did the required changes in it same like what you have suggested for but it didn't work. :-(

                                         

                                        I got the follow error when I tried with the solution that you have provided.

                                         

                                         

                                        Error in expression:
                                        ')' expected
                                        

                                         

                                        I would appreciate if you give any other suggestion.

                                         

                                        Thanks

                                         

                                        Attitude

                                          • Re: Variable to get the max(date)
                                            Miguel Angel Baeyens de Arce

                                            Hi,

                                             

                                            Just follow the error and close the missing bracket (if there is one)

                                             

                                            WHERE Date(@1) > Max(Date(Date#($(vStoreMaxLogin), 'DD/MM/YYYY')));
                                            

                                             

                                            By the way, note that we cannot check every line of the code we post, and sometimes we give ideas based on your code, and that code may have syntax errors or mispellings. It's always a good idea to not just copy and paste the code you get here, rather than check it first, understand what it does and how can it help you and then apply it to your data model and script.

                                             

                                            Regards.

                                             

                                            Miguel Angel Baeyens

                                            BI Consultant

                                            Comex Grupo Ibérica

                                              • Re: Variable to get the max(date)

                                                Hi Miguel

                                                 

                                                I am sorry If I am disturbing you. I too agree with you that whatever we do we need to understand and do. I always do that! Only sometimes when I get stuck I get confused.

                                                 

                                                I followed the error but there is no bracket which needs to be closed. Thats the reason I asked you are you able to find where I have done the mistake?

                                                 

                                                Thanks

                                                 

                                                Attitude

                                                  • Re: Variable to get the max(date)
                                                    Miguel Angel Baeyens de Arce

                                                    Hi,

                                                     

                                                    There's no problem, it's just you should be able to debug the load and see where the variables or the code is not correct. If it says it's expecting a bracket, it may be because the "Max()" is not getting the value from the variable properly.

                                                     

                                                    Did you try using that Max() piece of code in a textbox to see whether it returns the expected result?

                                                     

                                                    Miguel Angel Baeyens

                                                    BI Consultant

                                                    Comex Grupo Ibérica

                                                    • Re: Variable to get the max(date)

                                                      Hi,

                                                       

                                                      Miguel is correct, I forget to include the dollar sign expansion on my code.

                                                       

                                                      Look at the attached application with dollar sign expansion.

                                                       

                                                      -Sridhar

                                                        • Re: Variable to get the max(date)

                                                          Hi Sridhar

                                                           

                                                          Yes! I know that Miguel is correct :-). I have already added the $ symbol and tried but it is not working. Please look at discussion with Miguel for more details.

                                                           

                                                          Thanks

                                                           

                                                          Attitude

                                                            • Re: Variable to get the max(date)

                                                              Yes, i read all your discussion with Miguel`s. I though, you are not sure where / how to added the new piece of code to your application, That's why I have attached my example by re-define the code on my last post.

                                                               

                                                              is it throwing the error after you have added that new code to your application. could you please post your entire scripts here.

                                                               

                                                              - Sridhar

                                                                • Re: Variable to get the max(date)

                                                                  Hi Sridhar

                                                                   

                                                                  Yes it is giving below error. If you don't mind can you please wait for hours I will try to send you the entire script. Before that any guesses what could be the reason for the below error. 

                                                                   

                                                                  Error in expression:
                                                                  ')' expected
                                                                  • Re: Variable to get the max(date)

                                                                    Hi Sridhar

                                                                     

                                                                    Below is exactly the script which I am using it in my application. Requesting you to review it and let me know what is the change that is required here. It is little urgent. Hope you can understand! I will be waiting for your reply...

                                                                     

                                                                    /**************************************** 1st Block*************************************/
                                                                    
                                                                    //////////////////////
                                                                    
                                                                    if isnull(filetime('C:\Login.qvd')) then
                                                                    
                                                                    Login:
                                                                    
                                                                    LOAD @1 as LoginDate, 
                                                                         @2 as User, 
                                                                         @3 as Product
                                                                    FROM
                                                                    [C:\Status.txt]
                                                                    (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                                                    
                                                                    Concatenate
                                                                    
                                                                    LOAD Product, 
                                                                         LoginDate,
                                                                         User
                                                                    FROM
                                                                    $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                                                                    Store Login into Login.qvd (qvd);
                                                                    
                                                                    Drop table Logon;
                                                                    
                                                                    end if;
                                                                    
                                                                    ////////////////////////
                                                                    
                                                                    /**************************************** 2nd Block*************************************/
                                                                    
                                                                    Logon:
                                                                    
                                                                    LOAD LoginDate, 
                                                                         User, 
                                                                         Product
                                                                    FROM
                                                                    [C:\Logon.qvd]
                                                                    (qvd);
                                                                    
                                                                    Logon_Temp:
                                                                    
                                                                    Load Max(LoginDate) as LoginDate_Max Resident Logon:
                                                                    
                                                                    Let vStoreMaxLogin = Peek(LoginDate_Max,-1, Logon_Temp);
                                                                       
                                                                    Drop table Logon_Temp;
                                                                    
                                                                    CONCATENATE(Logon)
                                                                    
                                                                    LOAD @1 as LoginDate, 
                                                                         @2 as User, 
                                                                         @3 as Product
                                                                    FROM
                                                                    [C:\Status.txt]
                                                                    (txt, codepage is 1252, no labels, delimiter is ',', msq)
                                                                    WHERE Date(@1) > Max(Date#($(vStoreMaxLogin),’DD/MM/YYYY’));
                                                                      
                                                                    //CONCATENATE
                                                                    //LOAD Product, 
                                                                    //     LoginDate,
                                                                    //     User
                                                                    //FROM
                                                                    //$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
                                                                    //WHERE Date(@1) > Max(LoginDate) of session log;
                                                                    //Store Logon into Logon.qvd (qvd); 
                                                                    
                                                                    
                                                                    
                                                                      • Re: Variable to get the max(date)
                                                                        Miguel Angel Baeyens de Arce

                                                                        Hi,

                                                                         

                                                                        This is what I see at a first glance (usually typos when copying / pasting)

                                                                         

                                                                        /**************************************** 1st Block*************************************/
                                                                        
                                                                        //////////////////////
                                                                        
                                                                        if isnull(filetime('C:\Login.qvd')) then
                                                                        
                                                                        Login:
                                                                        
                                                                        LOAD @1 as LoginDate, 
                                                                             @2 as User, 
                                                                             @3 as Product
                                                                        FROM
                                                                        [C:\Status.txt]
                                                                        (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                                                        
                                                                        Concatenate
                                                                        
                                                                        LOAD Product, 
                                                                             LoginDate,
                                                                             User
                                                                        FROM
                                                                        $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                                                                        Store Login into Login.qvd (qvd);
                                                                        
                                                                        Drop table Logon;
                                                                        
                                                                        end if;
                                                                        
                                                                        ////////////////////////
                                                                        
                                                                        /**************************************** 2nd Block*************************************/
                                                                        
                                                                        Logon:
                                                                        
                                                                        LOAD LoginDate, 
                                                                             User, 
                                                                             Product
                                                                        FROM
                                                                        [C:\Logon.qvd]
                                                                        (qvd);
                                                                        
                                                                        Logon_Temp:
                                                                        
                                                                        Load Max(LoginDate) as LoginDate_Max Resident Logon; // semicolon instead of colon
                                                                        
                                                                        Let vStoreMaxLogin = Peek('LoginDate_Max', -1, 'Logon_Temp'); // Field name and table must be quoted
                                                                           
                                                                        Drop table Logon_Temp;
                                                                        
                                                                        CONCATENATE(Logon)
                                                                        
                                                                        LOAD @1 as LoginDate, 
                                                                             @2 as User, 
                                                                             @3 as Product
                                                                        FROM
                                                                        [C:\Status.txt]
                                                                        (txt, codepage is 1252, no labels, delimiter is ',', msq)
                                                                        WHERE Date(@1) > Max(Date#($(vStoreMaxLogin),'DD/MM/YYYY')); // Use regular single quotes instead
                                                                                                         // Max() will return a numeric value, Date() will return 
                                                                        //CONCATENATE
                                                                        //LOAD Product, 
                                                                        //     LoginDate,
                                                                        //     User
                                                                        //FROM
                                                                        //$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
                                                                        //WHERE Date(@1) > Max(LoginDate) of session log;
                                                                        //Store Logon into Logon.qvd (qvd); 
                                                                        
                                                                        

                                                                         

                                                                        Hope that helps.

                                                                         

                                                                        Miguel Angel Baeyens

                                                                        BI Consultant

                                                                        Comex Grupo Ibérica

                                                                          • Re: Variable to get the max(date)

                                                                            Hi Miguel

                                                                             

                                                                            Sorry for coming back to you again and again. As you said Max() will return a numeric. I tried something like below. When I saw the status in debugger mode it is not loading any data but it is throwing error message such as "Execution of script failed. Reload old data?". What could be the reason for this? Any suggestion please!

                                                                             

                                                                            Where Date(@1,'DD/MM/YYYY') > Date(Max(Date($(vStoreMaxLogin),'DD/MM/YYYY')),'DD/MM/YYYY');
                                                                            


                                                                            Still I am trying from my side and as suggested I am trying to understand how it works as well :-). So will update you later on!

                                                                             

                                                                            Thanks

                                                                             

                                                                            Attitude

                                                                              • Re: Variable to get the max(date)

                                                                                Hi,

                                                                                 

                                                                                You dont need to use max again in where clause script , since the variable which we have created is already storing the max value, you need to silmply use the variable and equate to the @1 field .

                                                                                 

                                                                                Your code should look like below one.

                                                                                 

                                                                                Where Date(@1,'DD/MM/YYYY') > Date($(vStoreMaxLogin),'DD/MM/YYYY');

                                                                                 

                                                                                Your code should look like below one.

                                                                                 

                                                                                - Sridhar

                                                                                  • Re: Variable to get the max(date)

                                                                                    Hi Sridhar

                                                                                     

                                                                                    Thanks! I have already noticed that and changed it as well but it is not working. Everytime I load the data it is storing all the data from the text into the qvd again again :-(

                                                                                     

                                                                                    Don't have any clue what could be the reason here. Sorry if it is so irritating for you.

                                                                                     

                                                                                    Thanks

                                                                                     

                                                                                    Attitude

                                                                                      • Re: Variable to get the max(date)

                                                                                        Hi Sridhar

                                                                                         

                                                                                        Sorry to trouble you! I have copied my script so that it is easy for you to help me out :-)

                                                                                         

                                                                                         

                                                                                        if isnull(filetime('C:\Login.qvd')) then
                                                                                        
                                                                                        Login:
                                                                                        
                                                                                        LOAD Date(@1,'DD/MM/YYYY') as LoginDate, 
                                                                                             @2 as User, 
                                                                                             @3 as Product
                                                                                        FROM
                                                                                        [C:\Status.txt]
                                                                                        (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                                                                        
                                                                                        Concatenate
                                                                                        
                                                                                        LOAD LoginDate,
                                                                                             User,
                                                                                             Product
                                                                                        FROM
                                                                                        [C:\Log.qvd]
                                                                                        (qvd);
                                                                                        
                                                                                        Store Login into Login.qvd (qvd);
                                                                                        
                                                                                        Drop table Logon;
                                                                                        
                                                                                        end if;
                                                                                        
                                                                                        Login:
                                                                                        
                                                                                        LOAD LoginDate, 
                                                                                             User, 
                                                                                             Product
                                                                                        FROM
                                                                                        [C:\Login.qvd]
                                                                                        (qvd);
                                                                                        
                                                                                        MAX1:
                                                                                        
                                                                                        Load Max(Date(LoginDate)) as MaxDate1 Resident Login where WILDMATCH (Product,'*abc*');
                                                                                        
                                                                                        Let vMax1 = Peek('MaxDate1',-1, MAX1); 
                                                                                        
                                                                                        //Drop table MAXDATE1;
                                                                                        
                                                                                        MAX2:
                                                                                        
                                                                                        Load Max(Date(LoginDate)) as MaxDate2 Resident Login where WILDMATCH (Product,'*cde*');
                                                                                        
                                                                                        Let vMax2 = Peek('MaxDate2',-1, MAX2);
                                                                                        
                                                                                        //Drop table MAX2; 
                                                                                        
                                                                                        //CONCATENATE
                                                                                        
                                                                                        //Logon:
                                                                                        
                                                                                        //CONCATENATE(Login)
                                                                                        
                                                                                        //Concatenate
                                                                                        
                                                                                        Login:
                                                                                        
                                                                                        LOAD Date(@1,'DD/MM/YYYY') as LoginDate, 
                                                                                             @2 as User, 
                                                                                             @3 as Product
                                                                                        FROM
                                                                                        [C:\Status.txt]
                                                                                        (txt, codepage is 1252, no labels, delimiter is ',', msq)
                                                                                        Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY'); 
                                                                                        
                                                                                        Store Login into Login.qvd (qvd);
                                                                                        
                                                                                        
                                                                                          • Re: Variable to get the max(date)
                                                                                            Miguel Angel Baeyens de Arce

                                                                                            Hi,

                                                                                             

                                                                                            You have to drop table Login, otherwise, since all tables you are loading are identical, they are being concatenated implicitly.

                                                                                             

                                                                                            Regards.

                                                                                             

                                                                                            Miguel Angel Baeyens

                                                                                            BI Consultant

                                                                                            Comex Grupo Ibérica

                                                                                              • Re: Variable to get the max(date)

                                                                                                yes, i about to tell you what miguel was telling.

                                                                                                 

                                                                                                 

                                                                                                if isnull(filetime('C:\Login.qvd')) then

                                                                                                 

                                                                                                 

                                                                                                Login:

                                                                                                 

                                                                                                 

                                                                                                LOAD Date(@1,'DD/MM/YYYY') as LoginDate,

                                                                                                     @2 as User,

                                                                                                     @3 as Product

                                                                                                FROM

                                                                                                [C:\Status.txt]

                                                                                                (txt, codepage is 1252, no labels, delimiter is ',', msq);

                                                                                                 

                                                                                                 

                                                                                                Concatenate

                                                                                                 

                                                                                                 

                                                                                                LOAD LoginDate,

                                                                                                     User,

                                                                                                     Product

                                                                                                FROM

                                                                                                [C:\Log.qvd]

                                                                                                (qvd);

                                                                                                 

                                                                                                 

                                                                                                Store Login into Login.qvd (qvd);

                                                                                                 

                                                                                                 

                                                                                                Drop table Logon;

                                                                                                 

                                                                                                 

                                                                                                end if;

                                                                                                 

                                                                                                 

                                                                                                Login:

                                                                                                 

                                                                                                 

                                                                                                LOAD LoginDate,

                                                                                                     User,

                                                                                                     Product

                                                                                                FROM

                                                                                                [C:\Login.qvd]

                                                                                                (qvd);

                                                                                                 

                                                                                                 

                                                                                                MAX1:

                                                                                                 

                                                                                                 

                                                                                                Load Max(Date(LoginDate)) as MaxDate1 Resident Login where WILDMATCH (Product,'*abc*');

                                                                                                 

                                                                                                 

                                                                                                Let vMax1 = Peek('MaxDate1',-1, MAX1);

                                                                                                 

                                                                                                 

                                                                                                //Drop table MAXDATE1;

                                                                                                 

                                                                                                 

                                                                                                MAX2:

                                                                                                 

                                                                                                 

                                                                                                Load Max(Date(LoginDate)) as MaxDate2 Resident Login where WILDMATCH (Product,'*cde*');

                                                                                                 

                                                                                                 

                                                                                                Let vMax2 = Peek('MaxDate2',-1, MAX2);

                                                                                                 

                                                                                                 

                                                                                                //Drop table MAX2;

                                                                                                 

                                                                                                 

                                                                                                //CONCATENATE

                                                                                                 

                                                                                                 

                                                                                                //Logon:

                                                                                                 

                                                                                                 

                                                                                                //CONCATENATE(Login)

                                                                                                 

                                                                                                 

                                                                                                //Concatenate

                                                                                                 

                                                                                                Drop Table Login,MAX1,MAX2 ;  ///Drop the table here.

                                                                                                 

                                                                                                Login:

                                                                                                 

                                                                                                LOAD Date(@1,'DD/MM/YYYY') as LoginDate,

                                                                                                     @2 as User,

                                                                                                     @3 as Product

                                                                                                FROM

                                                                                                [C:\Status.txt]

                                                                                                (txt, codepage is 1252, no labels, delimiter is ',', msq)

                                                                                                Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY');

                                                                                                 

                                                                                                 

                                                                                                Store Login into Login.qvd (qvd);

                                                                                                  • Re: Variable to get the max(date)

                                                                                                    Hi Miguel/Sridhar

                                                                                                     

                                                                                                    Below script doesn't seems to be working fine. As it is loading all the data again and again in the qvd instead of loading only new rows. Only for the first time I want it to load all the data as anyway it will be new only. Thats the reason I want to use the below where condition.

                                                                                                     

                                                                                                    Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY'); 
                                                                                                    


                                                                                                    Thanks

                                                                                                     

                                                                                                    Attitude

                                                                                                      • Variable to get the max(date)

                                                                                                        Hi Miguel/Sridhar

                                                                                                         

                                                                                                        Do you have any other suggestion? I am not able to make it out where I am doing the mistake. I am totally confused here. Please help me out!

                                                                                                         

                                                                                                        Thanks

                                                                                                         

                                                                                                        Attitude

                                                                                                          • Re: Variable to get the max(date)

                                                                                                            Attitude,

                                                                                                             

                                                                                                             

                                                                                                            As far i know, the above code should work.

                                                                                                             

                                                                                                             

                                                                                                            One debugging step would be, take this vMax1 variable to text box after loading and check whether this variable is giving value or not. if this variable gives value then check manually, whether captured value is the max of the logindate from you Login table.

                                                                                                             

                                                                                                             

                                                                                                            Try commenting one by one table script and load them one by one and try to trouble shoot.

                                                                                                             

                                                                                                             

                                                                                                            - Sridhar

                                                                                                              • Re: Variable to get the max(date)

                                                                                                                Hi Sridhar

                                                                                                                 

                                                                                                                I have already performed the debugging steps that you have provided. vMax1 gives the exactly the value I am looking for.

                                                                                                                 

                                                                                                                Only problem which I have is it is storing all the rows from the text file to the qvd again. This is the problem which needs to be resolved :-(

                                                                                                                 

                                                                                                                Below script is still giving trouble. It is not working as expected. Suggest something for the below one. What exactly we need is that it should not load existing data in to the qvd. But the below script loads all the data again in the qvd. :-(

                                                                                                                 

                                                                                                                Login:
                                                                                                                  
                                                                                                                LOAD Date(@1,'DD/MM/YYYY') as LoginDate, 
                                                                                                                     @2 as User, 
                                                                                                                     @3 as Product
                                                                                                                FROM
                                                                                                                [C:\Status.txt]
                                                                                                                (txt, codepage is 1252, no labels, delimiter is ',', msq)
                                                                                                                Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY'); 
                                                                                                                
                                                                                                                

                                                                                                                Thanks

                                                                                                                 

                                                                                                                Attitude

                                                                                                                • Re: Variable to get the max(date)

                                                                                                                  Hi Sridhar/Miguel

                                                                                                                   

                                                                                                                  As requested I have attached sample of my application with the data source. Hope it will be easy for you to understand about issue which I am facing in a better way. Once you come across with the solution please do let me know. It is very very urgent now :-(

                                                                                                                   

                                                                                                                  Thanks

                                                                                                                   

                                                                                                                  Attitude

                                                                                                        • Re: Variable to get the max(date)

                                                                                                          Hi Miguel/Sridhar

                                                                                                           

                                                                                                          If drop Login,MAX1 and MAX2. QVD contains contains only those records which are getting loaded after the drop statement. Whereas I want to concatenate those records which are getting loaded after the drop statement.

                                                                                                           

                                                                                                          Hope you have understood now regarding what exactly I am looking for.

                                                                                                           

                                                                                                          Also once all the records which is there after the drop statement gets concatenated then from next time when I load any if no new records are there then no rows will be concatenated. If any new records are there then only those new records should be concatenated.

                                                                                                           

                                                                                                          Thanks

                                                                                                           

                                                                                                          Atttitude