Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

vaibhav_claylog
New Contributor

Date field coming as General value in qlik

I have a Dates of each month in the column

When i am loading the date it is coming as a general value.

I tried changing the excel format to date. Still it is coming as a general value.

Please see the date image of the excel and QLik sense

1 Solution

Accepted Solutions

Re: Date field coming as General value in qlik

Try with Num#() instead of Floor()

jj:

CrossTable(Date1,Budget,6)

LOAD

    "Employee Code",

    Particulars,

    Speciality,

    "Distribution type",

    Distribution_Channel,

    "Year",

    "42736",

    "42767",

    "42795",

    "42826",

    "42856",

    "42887",

    "42917",

    "42948",

    "42979",

    "43009",

    "43040",

    "43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);


Load

Date(Num#(Date1)) as Date2

Resident jj;

Drop table jj;

20 Replies

Re: Date field coming as General value in qlik

Use The Crosstable Load to load your data and then format the date as date in a resident load

thiago_justen
Valued Contributor III

Re: Date field coming as General value in qlik

As stalwar1‌ said, you need to do something like this:

TEMP:

crosstable (Date, Value)

LOAD * from YOURDB;


FINAL:

LOAD

    *,

    DATE(FLOOR(Date)) As Date

Resident TEMP;


Drop Table TEMP;



Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
vaibhav_claylog
New Contributor

Re: Date field coming as General value in qlik

stalwar1

I am already using cross table.

Kindly have a look at my script

jj:

CrossTable(Date1,Budget,6)

LOAD

    "Employee Code",

    Particulars,

    Speciality,

    "Distribution type",

    Distribution_Channel,

    "Year",

    "42736",

    "42767",

    "42795",

    "42826",

    "42856",

    "42887",

    "42917",

    "42948",

    "42979",

    "43009",

    "43040",

    "43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);


Load

Date(Floor(Date1)) as Date2

Resident jj;

Drop table jj;


thiago_justen
Valued Contributor III

Re: Date field coming as General value in qlik

Try converting date field like this

Date (Num# (Date1))

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

Re: Date field coming as General value in qlik

Try with Num#() instead of Floor()

jj:

CrossTable(Date1,Budget,6)

LOAD

    "Employee Code",

    Particulars,

    Speciality,

    "Distribution type",

    Distribution_Channel,

    "Year",

    "42736",

    "42767",

    "42795",

    "42826",

    "42856",

    "42887",

    "42917",

    "42948",

    "42979",

    "43009",

    "43040",

    "43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);


Load

Date(Num#(Date1)) as Date2

Resident jj;

Drop table jj;

vaibhav_claylog
New Contributor

Re: Date field coming as General value in qlik

stalwar1

Thanks for the answer . It worked.

I tried using YTD script available on the community.

I am using this script for the YTD.

Sum({<Date2={'>=$(=(YearStart(AddYears(Max(Date2),0))))<=$(=addYears(Max(Date2),0)))'}>}Budget)/100000

I am not able to obtain YTD. Can you help me with this?

Re: Date field coming as General value in qlik

Try this

This Year YTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2))))<=$(=Date(Max(Date2)))"}>}Budget)/100000

LYTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2), -1)))<=$(=Date(AddYear(Max(Date2), -1)))"}>}Budget)/100000

vaibhav_claylog
New Contributor

Re: Date field coming as General value in qlik

stalwar1

YTD is not coming by using that expression.

My data consist of only 2017.

I tried both your expressions.

Re: Date field coming as General value in qlik

I see no reasons why they should not work... would you be able to share a sample where I can see it not working? or share images?

Community Browser