29 Replies Latest reply: Jan 21, 2015 7:16 AM by neetha P RSS

QVD Creation

sanjayrajsharmas

Hi All,

 

 

How to create QVD files, Different ways to create a QVD

 

 

SRS

  • QVD Creation
    Christophe Brault

    Hi,

     

    After your load statement, you can simply write this code to store a table in a QVD :

     

    STORE YourTable into YourQVDName.QVD;

  • QVD Creation
    janardhan

    Hi Sanjay ,

     

    Nilesh is correct but he missed one thing

     

     

    tablename1:

     

    load * from

     

    excel1;

     

     

     

    store tablename1 into tablename1.qvd;

     

     

     

    tablename2:

     

    load *

     

    from excel2;

     

     

     

    store tablename2 into tablename2.qvd;

  • Re: QVD Creation
    Anand Chouhan

    Hi,

     

    Use some thing like this in the script

     

    Table1:

     

    load * from

    D1;

     

    store Table1 into Table1.qvd;

     

    Table2:

     

    NoConcatenate

     

    load * from

    D1;

     

    store Table2 into Table2.qvd;

     

     

    Regards,

    Anand

  • Re: QVD Creation
    Sokkorn Cheav

    Hi,

     

    I try your script and working for me. Let see in the attached files.

     

    Edit: Put all files in the same folder.

     

    Regards,

    Sokkorn

  • Re: QVD Creation
    Anand Chouhan

    Hi,

     

     

    If you not need the loaded tables so delete it and use the QVD files.

     

     

     

    Regards,

    Anand

    • QVD Creation
      Ruby Otero

      may i ask... is there an effect of you issue drop table after the store statement?

       

      and also what is nonconcaatenate use for?

      • Re: QVD Creation
        Anand Chouhan

        Hi,

         

        No there is not any effect if you put drop statement after the store statement and noconcatenate command is used to not concatenate to tables like if there is any tables name is same and fileds name is also same so in qlikview is automatically concatenate to the previous table noconcatenate is forcelly used to not concatenate to the table previous table.

         

        Regards,

        Anand

  • Re: QVD Creation
    Nelson Adao

    Hi all!

    This is the error that is not allowing me to store the tables into QVDs:

    Cannot open file 'C:\Users\X\Desktop\Qlikview Test \Test.dbo."vw_Cha"' The system cannot find the file specified.

    Cha:
    load * from
    Test.dbo."vw_Cha"

     

    Anyone can help ?

     

    Thanks in advance for your help!

    N.

  • Re: QVD Creation
    Raj Kumar

    Hi Nelson

     

     

     

     

    THIS  STORES ALL THE TABLES LOADED INTO QVDS

     

    //Loop through all of the table names and store them to QVD.

     

    LET vTableVol = NoOfTables();//Get the number of tables loaded

     

    FOR i = 0 TO $(vTableVol) - 1 ;

     

    LET vTableNameS = TableName($(i));


    IF '$(vTableNameS)' <> 'Vars' AND '$(vTableNameS)' <> 'Tables' AND '$(vTableNameS)' <> 'DBInfo' THEN //Do not load the system tables in the main QVD folder

     

    STORE $(vTableNameS) INTO  $(vQVDFilePath)$(vTableNameS).qvd (qvd);

     

    ELSE

     

    STORE $(vTableNameS) INTO  $(vQVExSysPath)$(vTableNameS).qvd (qvd);

    • Re: QVD Creation
      Nelson Adao

      Hi Raj!! Thanks a lot for your quick reply!

      I have reloaded my script with this piece of code and it stored the tables as variables but did not create a separate .QVD file. Would you be so kind to explain me how you could do that ?

      Thanks.

  • Re: QVD Creation
    neetha P

    Hi,

     

    Check below:

     

    TempTable:
    LOAD Distinct Year([Accounting Period]) AS YearField
    FROM QVD\Main.qvd(qvd)
    Order by Year([Accounting Period]) asc;

    NoConcatenate

    Table:
    Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
    Resident TempTable;

    Let vMin=Peek('minYear', 0, 'Table');
    Let vMax=Peek('maxYear', 0, 'Table');

    FOR i=$(vMin) to $(vMax)
    LET vYear = Peek(' YearField ',$(i),'TempTable');

    ConsolidateYearWise:
    Concatenate
    LOAD * ,
    Year([Accounting Period]) AS YearFlag
    FROM QVD\Main.qvd(qvd)
    Where Year([Accounting Period]) = '$(vYear)';

    Next

    STORE ConsolidateYearWise into ConsolidateData.qvd;

    DROP Table ConsolidateYearWise;

    DROP Table TempTable;
    DROP Table Table;


    //Yearly QVD:

    TempTable:
    LOAD Distinct Year([Accounting Period]) AS YearField
    FROM QVD\Main.qvd(qvd)
    Order by Year([Accounting Period]) asc;

    NoConcatenate

    Table:
    Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
    Resident TempTable;

    Let vMin=Peek('minYear', 0, 'Table');
    Let vMax=Peek('maxYear', 0, 'Table');

    FOR i=$(vMin) to $(vMax)
    LET vYear = Peek(' YearField ',$(i),'TempTable');

    SeparateYearWise:
    NoConcatenate
    LOAD * FROM QVD\Main.qvd(qvd)
    Where Year([Accounting Period]) = '$(vYear)';

    STORE SeparateYearWise into TableName_$(vYear).qvd;
    DROP Table SeparateYearWise;

    Next

    DROP Table TempTable;
    DROP Table Table;


    //Past 6 months QVD:

    LET vAccountingPeriod = num(addmonths(today(),-6));

     

    SixMonthsData:
    LOAD * FROM QVD\Main.qvd(qvd)
    Where [Accounting Period]>=$(vAccountingPeriod);

    STORE SixMonthsData into TableName_SixMonthsData.qvd;
    DROP Table SixMonthsData;

  • Re: QVD Creation
    venkatasreekanth2020

    Hi,

     

    Use like this in the script

     

    TableName:

     

    load * from

    D1;

     

    store TableName into TableName.qvd;

  • Re: QVD Creation
    Nelson Adao

    Could you also be so kind and help me with the following?

    I would like to convert all date fields across my application from DD/MM/YYYY HH:MM:SS to DD/MM/YYYYMy question is, can I make it directly in my initial script rather than in an object expression, so that I will not have to amend it each time I insert a new object?

    Thanks for your time!

    Kind regards,

    Nelson.