topic Re: Formatting problem? in QlikView

Formatting problem?

datanibbler — Fri, 10 Apr 2015 09:45:39 GMT

Hi,

I am facing an issue I just cannot get to the bottom of:

- I have a file where there is a date_value (originally in the format of 'DD.MM.YYYY') in every row;

- I can easily format that to the format of 'YYYYMMDD' (to avoid the dots which could be problematic in other functions)

- (Background: That file is an aggregation of my base_file)

- Then I want to open a loop and parse that small file line by line (day by day, that is) and extract all info from the base_file where I have
that date.

- In the WHERE clause, I use the exact same date_format (I have to reformat it, but I know that that works as the file_to_parse also comes from that same base_file)

<=> Still I get 0 records!

I will attach two things to this post:

- The qvd file I have created from that base_file

- The script code I am currently using

Thanks a lot for any help here!

Best regards,

DataNibbler

P.S.: I have checked several times that my date_variable is numeric, just like the field in the table, so I cannot understand why comparing them should produce 0 records ...

Re: Formatting problem?

Anonymous — Fri, 10 Apr 2015 10:06:26 GMT

I made a Little test with yur script and with a small inline table

Ergebnisse_Vgl_#1:
Load * Inline [
Versanddatum_BMW, Endkosten
01.04.2015, 1000
02.04.2015, 2000
];

The result is also 0 datarows.

but when i Change Tagesdatum2 to Tagesdatum (which still is in the old data Format), I get 1 datarow

I donot know why comparison of 2 different dateformats is working (YYYYMMDD compared to DD.MM.YYYY')

LET v_current_date = PEEK('Versanddatum', 0, 'Ergebnis_#1_Tagessummen');

Does it help in your case too?

Re: Formatting problem?

Fri, 10 Apr 2015 10:09:13 GMT

Hi Data,

looks like Versanddatum2 is in the YYYYMMDD format? but in your QVD Versanddatum_BMW has the decimals.

So don't think you will just be able to date your where clause.

You'll need to use Date# first then date in your where clause

DATE( DATE#(Versanddatum_BMW, 'DD.MM.YYYY'), 'YYYYMMDD')

hope that helps

Joe

Re: Formatting problem?

datanibbler — Fri, 10 Apr 2015 10:16:06 GMT

Hi,

it works now - but it's very strange and the worst is, I have no idea why it works now and didn't work before ...

I wrapped the date (in the old format 'DD.MM.YYYY') in quoted and in another DATE() function now - that way works.

=> It now looks like >> DATE('$(v_current_date)') <<

@ JoeSimmons

That with the decimals could be - but actually I use the very same DATE() function on the very same table twice - first I aggregate the base_file to a table holding all the distinct dates and then I parse that to split the very same base_file into slices - so there's no apparent reason why the results should not be comparable ... I'll try anyway.

Re: Formatting problem?

Fri, 10 Apr 2015 10:26:08 GMT

Hmm interesting, I was thinking the decimals was causing problems if it wasn't treated the value as a proper number and so failing the date function. If that's not the case though I'm not sure, would be good if you could upload an example or your full script.

As you say should be fine if it's form the same base file

Joe

Re: Formatting problem?

datanibbler — Fri, 10 Apr 2015 11:14:29 GMT

Hi Joe,

that's very simple: If you load all the fields from the qvd file I have attached - that is the base_file (I have a STORE command just before that piece of code I attached).

Best regards,

DataNibbler

Re: Formatting problem?

Fri, 10 Apr 2015 13:01:18 GMT

Try the below, as it's a real date field there will be a numerical value underlying, so should be able to use num instead

LET v_current_date = Num(PEEK('Versanddatum2', 0, 'Ergebnis_#1_Tagessummen'));

Drop table Ergebnis_#1_Tagessummen;

// LET v_current_date = DATE('$(v_current_date)', 'YYYYMMDD');

BMW_only_Tagessumme:

NoConcatenate

LOAD

*,

DATE(Versanddatum_BMW, 'YYYYMMDD') as Versanddatum_BMW_3

RESIDENT Ergebnisse_Vgl_#1

WHERE Num(Versanddatum_BMW) = '$(v_current_date)'

;