Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sibrulotte
Contributor III

Append table to archive

Hi,

I have a table that has comments that are input fields.

INPUT FIELD Commentaires;

Référentiel_Règles:
LOAD
Timestamp(now(),'YYYY-MM-DD hh.mm') as Date_archive,

NUM_REGLE,
 
Description_regle,
 
[Description du contrôle],
  
Commentaires,
  
applymap('BRIS_MAP', NUM_REGLE, NULL()) as Bris,
    
Nom_variable_regle
FROM
[$(ClientDataDir)\Conformité.xlsx]
(
ooxml, embedded labels, table is Regles);

I want to keep a log of this table, so I tried to create a chart object that I export via macro to csv file using

Sub ExportToFile

set sObject = ActiveDocument.GetSheetObject("CH01")

sObject.Export "C:someplace\TestFile.csv", "; "

End Sub

Any suggestions on how to append new comments/now() values created from the first table to a log file?

1 Solution

Accepted Solutions
sibrulotte
Contributor III

Re: Append table to archive

I ended up just using a macro to append my current second table, Référentiel_Règles, to an excel file that I never load in my model. It works great since the current Input field value is always in memory, never replaced, and there's no inteference with older log values.

Here's the VBA used:

Sub Test 

   ExcelAppend "CSmiley Frustratedome palce/TestFile.xlsx", "CH32" 

End Sub 

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID) 

   ' Create an instance of Excel 

   SET objExcelApp = CREATEOBJECT("Excel.Application") 

    

   ' Open workbook 

   WITH objExcelApp 

      .DefaultSaveFormat = xlWorkbookNormal 

      .DisplayAlerts = FALSE 

      .Workbooks.Open strExcelAppenFile 

      .DisplayFullScreen = FALSE 

      .Visible = FALSE 

   END WITH 

    

   ' Set worksheet 

   SET objExcelSheet = objExcelApp.Worksheets(1) 

       

   ' Set Excel used range 

   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 

   ' Last used row in column A 

   intExcelLastRow = objExcelRange.Row 

   ' Set object to append from 

   SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID) 

   ' Loop all rows of the object except first header row 

   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 

      ' Loop all columns of the object 

      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 

         ' Get object data 

         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 

         ' Add that data to Excel cell 

         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 

      NEXT 

   NEXT 

    

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

END SUB 

6 Replies
sibrulotte
Contributor III

Re: Append table to archive

As of right now, I am just loading the exported file with matching name colomns.

INPUTFIELD Commentaires;
Archives_Regles:
LOAD Date as Date_archive,
    
Règle as NUM_REGLE,
    
État as Bris,
    
Description as Description_regle,
    
Commentaire as Commentaires
FROM

(
txt, utf8, embedded labels, delimiter is ';', msq);

INPUTFIELD Commentaires;
Référentiel_Règles:

LOAD

 
Timestamp(now(),'YYYY-MM-DD hh.mm') as Date_archive,
 
NUM_REGLE,
    
Description_regle,
     Commentaires,
    
applymap('BRIS_MAP', NUM_REGLE, NULL()) as Bris  
FROM
[$(ClientDataDir)\Conformité.xlsx]
(
ooxml, embedded labels, table is Regles);

One big table with all values archived.

My problem is now to have a chart that shows only the max value for "Date_archive"

so that the users input only for the now() rows...

Not scripting, so I might ask elsewhere.

konstantin
Contributor III

Re: Append table to archive

use Max(Date_archive) in the expression

sibrulotte
Contributor III

Re: Append table to archive

Hi,

I know I know, but I'm not going to show the date i my chart, so I have to do a variable that will evaluate the max(Date_archive) and then use that variable in a set analysis of the expression in the chart.

sibrulotte
Contributor III

Re: Re: Append table to archive

I am encountering a problem with this.

My chart has a set analysis to retrieve only the last value of my archive file:

sum({<Date_archive ={$(vDate_archive)}>} Bris

variable overview:

vDate_archive  = chr(39)&maxstring(Date_archive)&chr(39)


Here is the problem:

When I load the excel file that has the original data, the comments for the last date get replaced by what's in the excel file.

I want to show the value from the last archived date.

I thus need a maxstring -1 or a second-to-last-string...

Does that exist?

sibrulotte
Contributor III

Re: Re: Append table to archive

Also, for some reason, Now() is stuck at 25-09-2014 15:09...

I traced it in a seperate variable and always get that timestamp... :/

sibrulotte
Contributor III

Re: Append table to archive

I ended up just using a macro to append my current second table, Référentiel_Règles, to an excel file that I never load in my model. It works great since the current Input field value is always in memory, never replaced, and there's no inteference with older log values.

Here's the VBA used:

Sub Test 

   ExcelAppend "CSmiley Frustratedome palce/TestFile.xlsx", "CH32" 

End Sub 

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID) 

   ' Create an instance of Excel 

   SET objExcelApp = CREATEOBJECT("Excel.Application") 

    

   ' Open workbook 

   WITH objExcelApp 

      .DefaultSaveFormat = xlWorkbookNormal 

      .DisplayAlerts = FALSE 

      .Workbooks.Open strExcelAppenFile 

      .DisplayFullScreen = FALSE 

      .Visible = FALSE 

   END WITH 

    

   ' Set worksheet 

   SET objExcelSheet = objExcelApp.Worksheets(1) 

       

   ' Set Excel used range 

   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 

   ' Last used row in column A 

   intExcelLastRow = objExcelRange.Row 

   ' Set object to append from 

   SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID) 

   ' Loop all rows of the object except first header row 

   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 

      ' Loop all columns of the object 

      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 

         ' Get object data 

         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 

         ' Add that data to Excel cell 

         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 

      NEXT 

   NEXT 

    

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

END SUB 

Community Browser