Floor, date func issue with excel loaded data

[Digvijay_Singh]

Hi,

I successfully answered one community question(209863) but faced weird situation(for me at least!) when floor and date functions were not working on some fields. these fields were loaded from excel after transforming through transformation wizard.

My question is -    in below script these two lines don't work, doesn't make any difference in output

Floor(ID) as ID,

  Date(Floor([Date Bought])) as [Date Bought],

But when I use new fields(added '1' at the end), it works, Why it is so?

Floor(ID) as ID1,

  Date(Floor([Date Bought])) as [Date Bought1],

Here is the script, input excel file and output image

T1:
LOAD @1 as ID,
    @2 as [Date Bought],
    @3 as [Product Bought],
    @4 as [Last Name]
FROM
[allign rows.xlsx]
(ooxml, no labels, table is Sheet2, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'ID'))),
Remove(Col, Pos(Top, 3)),
ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),
ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),
Replace(4, top, StrCnd(null)),
Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))
));


NoConcatenate
Final:
Load
  Floor(ID) as ID,
  Date(Floor([Date Bought])) as [Date Bought],
  [Product Bought],
  Subfield([Last Name],' ',2) as [Last Name]
Resident T1;
Drop Table T1;

Resident T1;
Drop Table T1;

Before making ID to ID1 - here is the output

After making change it is showing perfectly -

[swuehl]

I believe, that's because the text representation of a numeric value (like a Date() format of the underyling numeric value) is determined by the first representation loaded in your script, and this happens when loading T1.

[Digvijay_Singh]

Thanks Stefan! I was thinking on same line but both fields are right aligned, means interpreted as numeric then why not allowing to remove digits after decimal?

[swuehl]

If you only load T1 table, you will see that ID and date are already loaded as integer values (e.g. by using the table viewer), they are just formatted showing decimal places.

So, when using your resident load, you are loading to a different output table, but using the same symbol table where the values and their text representation is stored.

Since the numbers are already integers, applying floor() doesn't change anything, the numeric values keep the same.

Hence the symbols won't be added to the symbol table, the existing one from T1 load will be used to reference values in your ouput data table.

Besides this, you could do above in one load.

T1:

LOAD   

      Floor(@1) as ID,

       Date(Floor(@2)) as [Date Bought],

       @3 as [Product Bought],

       Subfield(@4,' ',2) as [Last Name]

FROM

[allign rows.xlsx]

(ooxml, no labels, table is Sheet2, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'ID'))),

Remove(Col, Pos(Top, 3)),

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

Replace(4, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

[HirisH_V7]

Hi,

Check this,

Get the Dates Right

Hope this helps,

Hirish

[Kushal_Chawda]

Hi,

As stefan suggested, you can directly do this in single load or else you can perform the same on preceding load

T1:
Load
floor(ID) as ID,
floor([Date Bought]) as [Date Bought],
[Product Bought],
Subfield([Last Name],' ',2) as [Last Name];
LOAD @1 as ID,
@2 as [Date Bought],
@3 as [Product Bought],
@4 as [Last Name]
FROM
[allign rows.xlsx]
(ooxml, no labels, table is Sheet2, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'ID'))),
Remove(Col, Pos(Top, 3)),
ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),
ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),
Replace(4, top, StrCnd(null)),
Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))
));

[Kushal_Chawda]

Issue here is when you are loading the data from excel Qlikview already loaded it as integer. So when you are trying to change the format on resident, Qlikview says, it is already integer why you want to change? But preceding load works here, because QliView performs the operation single load i.e conversion happens in single load.

[Digvijay_Singh]

Hi Stefan,

Last question.

I still feel at least the date function should change the text representation as per the set date format. When we change format through Date function on any numeric/date field, normally text representation changes for already loaded fields.

Regards,

[Digvijay_Singh]

Thanks Kush,

I also checked that things work while loading the first table, I was confused when functions were not responding during resident load. I still feel the date should have worked and should changed the text representation. If I am applying certain date format on any numeric field, normally we see the output(text representation) as per new format.( On the already loaded fields).

Regards,

DJ

[swuehl · Accepted solution]

Hi Digvijay Singh,

I believe the Date() function is working o.k., but it's just trying to set the format for the record values in the output table.

As I tried to explain above, as soon as QlikView inserts the record's value into the symbol table, where the field format is stored, it encounters that the value is already set (in your first load). So it won't insert the value (and the new format), but just reuse the pointer to the existing values (and its existing format).

In general, that's why load order of field values is quite important in QlikView.

Hope this helps,

Stefan