Field Type not restored from QVD/QVX?

[robinrouleur]

Can anyone tell me whether Field types are meant to be loaded with the values from QVD and QVX files?

When I load a virgin QVW from either QVD or QVX source files everything appears as Mixed number format even though the fields were stored with formats like Interval and Time etc?  The result is, for example, that Interval fields are displayed as decimal numbers rather than hh:mm:ss.  This can be fixed by going to Document Properties > Number and changing from Mixed to Interval but I was not expecting that to be necessary?

Regards

Robin

[miguelbraga]

Hey there,

Dates and Numbers are automatically transformed to their correspondent number. If you want to preserve the type, save if in the QVD with text format so you can get the format right the next time you use it

Best regards,

D.A. MB

[jonathandienst]

The data type is stored in a QVD (and I assume also a QVX) as tags. But whether its stored will depend on how the field is handled in the QVD generating QVW.

This a sample of the metadata recorded in XML at the start of the QVD file:

<QvdFieldHeader>

    <FieldName>ADate</FieldName>

    <NumberFormat>

        <Type>DATE</Type>

        <nDec>0</nDec>

        <UseThou>0</UseThou>

        <Fmt>yyyy/MM/dd</Fmt>

        <Dec></Dec>

        <Thou></Thou>

    </NumberFormat>

    <Comment></Comment>

    <Tags>

        <String>$numeric</String>

        <String>$integer</String>

        <String>$timestamp</String>

        <String>$date</String>

    </Tags>

</QvdFieldHeader>

<QvdFieldHeader>

    <FieldName>AnInterval</FieldName>

    <NumberFormat>

        <Type>INTERVAL</Type>

        <nDec>0</nDec>

        <UseThou>0</UseThou>

        <Fmt>hh:mm</Fmt>

        <Dec></Dec>

        <Thou></Thou>

    </NumberFormat>

    <Comment></Comment>

    <Tags>

        <String>$numeric</String>

    </Tags>

</QvdFieldHeader>

To make sure that the data type is correctly interpreted and formatted, make sure that they are explicitly converted and/or formatted (and/or set the number format in the Document Properties | Numbers tab - not the Chart Properties)

The tags follow the same principles.

[Digvijay_Singh]

QVD fields will have format as they were set in script only. I don't think it has any relation with  Document Properties>Number setting. Once you set formatting through Document Properties>Number, it will keep same format in front end only across different visualization objects/sheets of that particular document.

So if I undrstood your doubt, I suggest to handle formatting in script only so that it can survive while using QVD in different documents.

[jonathandienst · Accepted solution]

I think that the Document Properties are not important here - but the conditions in which the formats are set in the QVD are not documented so I just wanted to cover all bases.

But if you load all dates and intervals with an explicit conversion, they will be stored in the QVD with that type and the relevant tags:

Data:

LOAD ID,

  AString,

  Num(AValue, '# ##0.000', '.', ' ') as aNum,

  Date(ADate, 'yyyy/MM/dd') as ADate,

  Interval(AnInterval, 'hh:mm') as AnInterval

Inline

[

  ID, AString, AValue, ADate, AnInterval

  1, dog, 1.850, 2016/09/23, 0.25

  2, cat, -8934, 2015/01/01, 0.067

];

STORE Data into Test.qvd;

//(Meta data contains formats for AValue, ADate and AnInterval)

[robinrouleur]

Thanks Jonathan, The fields are set as Interval in the QVD/X generating QVW (under Document Properties>Number) and they are read into the empty QVW using an optimized load.  A simple load with no field operations is important as the front end is being developed by a different team with no back end experience.  It appears that the fields are being stored as Type "UNKNOWN".  Here's a segment of the QVX - any suggestions?

     <QvxFieldHeader>

       <FieldName>Call_duration</FieldName>

       <Type>QVX_QV_DUAL</Type>

       <Extent>QVX_QV_SPECIAL</Extent>

       <NullRepresentation>QVX_NULL_NEVER</NullRepresentation>

       <CodePage>65001</CodePage>

       <ByteWidth>0</ByteWidth>

       <FixPointDecimals>0</FixPointDecimals>

       <FieldFormat>

         <Type>UNKNOWN</Type>

         <nDec>0</nDec>

         <UseThou>0</UseThou>

         <Fmt></Fmt>

         <Dec></Dec>

         <Thou></Thou>

       </FieldFormat>

       <BigEndian>false</BigEndian>

     </QvxFieldHeader>

[robinrouleur]

Yes, this may be the solution.  Currently I am loading the QVD/X generating QVW using Load * from CSVs and then setting the formats under Document Properties>Number as this was easier that explicit formatting in the load stgatement.  I'll try it out and let you know.

Thanks

Robin

[jonathandienst]

I think you proved Digvijay's point

[#9]

Hi Robin,

As discussed here, My query here is i am loading Qvd's dynamically from one folder and i am displaying like Name of the Qvd, File Size, Data Type in dashboard but problem here is i am getting some columns data type as 'UNKNOWN' , same Columns which are having data type as Varchar in Teradata Database

My Script is as follows :

FOR each file in FileList(vShareDir&'\*.qvd')

LET vFileName = mid(file, index(file, '\', -1) + 1, 99);

     QvdFields:

LOAD

          1                      as FieldCount,

          '$(vFileName)'         as [File Name],

          FieldName              as [Field Name],

          NoOfSymbols            as [No Of Symbols],

         [NumberFormat/Type] as [Data Type]

     FROM [$(file)] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

Kindly Help me Guys,

Regards,

Reddi Kishor