Problem with txt or tabdeliminated file?

[jagannalla]

Hi,

I'm creating a qvd file with field CaptureDateTime and by using resident load and peek function i can able to catch Maximum date and storing this date in one variable.

For Eg:

Fact_Sales:

LOAD CaptureDateTime

FROM

D:\TestQlikViewExamples\Fact_Sales.qvd

(qvd);

Resident_table:

LOAD

max(CaptureDateTime) as maxCaptureDateTime

Resident Fact_Sales;

LET vDate=Peek('maxCaptureDateTime',-1,Resident_table);

- When i take maxCaptureDateTime in listbox i can see maximum date and also in varaible vDate. But, If i create txt or tabdeliminted file with field CaptureDateTime and trying to catch maximum date it is not working.

For Eg:

Fact_Sales:

LOAD CaptureDateTime

FROM

D:\TestQlikViewExamples\Fact_Sales.txt

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

Resident_table:

LOAD

max(CaptureDateTime) as maxCaptureDateTime

Resident Fact_Sales;

LET vDate=Peek('maxCaptureDateTime',-1,Resident_table);

- I'm creating qvd,txt & tabdeliminated files using this code

//STORE Fact_Sales into Fact_Sales.qvd(qvd);

//STORE Fact_Sales into Fact_Sales.txt(txt);

//STORE Fact_Sales into Fact_Sales.txt(txt, delimiter is '\t');

Can anyone help me why it is not showing maximum date. And one more thing is if i generate tabdeliminated file with less data and if load this file in editscript it shows Delimiter with Tab. But when i create tabdeliminated file with large amount of data it shows delimiter with Space.

[kaushiknsolanki]

HI,

     As i can see from your code is you are exporting (Storing) Fact_Sales table where you dont have Max Date.

     So when the statement to store the table executes, it will store whatever you have in table Fact_Sales.

Regards,

Kaushik Solanki

[jagannalla]

In my code i've shown you with already created qvd file with single field capturedatetime..

Actually, the think is i had already created qvd & txt files. I checked with qvd file  as i shown in above post to see max capturedatetime. But when i tried with txt file i'm not able to see max capture date time.

for your reference i'm attaching qvd,txt & tabdelimited files also.

[kaushiknsolanki]

Hi,

    I think its because when you fetch data from text file all the fields are considered as string fields.

    So when you load data from text file, you need to tell Qlikview that this field is date field.

    So try this when you want to load data from text file.

Fact_Sales:

LOAD Date(Date#(CaptureDateTime,'Format in which you have Date (for ex. DD/MM/YYYY)'),'DD/MM/YYYY') as CaptureDateTime

FROM

D:\TestQlikViewExamples\Fact_Sales.txt

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

    Hope you understood.

Regards,

Kaushik Solanki

[jagannalla]

There is no problem in date format.. only problem in residenttable

Resident_table:

LOAD

max(CaptureDateTime) as maxCaptureDateTime

Resident Fact_Sales;

LET vDate=Peek('maxCaptureDateTime',-1,Resident_table);

-Now if you take in listbox maxCaptureDateTime. I'm not able to see maximum date.

[kaushiknsolanki]

Hi,

     Try in this way.

Resident_table:

LOAD

max(CaptureDateTime) as maxCaptureDateTime

Resident Fact_Sales;

LET vDate=Peek('maxCaptureDateTime',0,Resident_table);

I have changed -1 to 0.

Regards,

Kaushik Solanki

[jagannalla]

Ok kaushik i'll try and get back to you right now i'm not in office.