Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

MVP
MVP

Re: Date issue (string to year-month)

Use Evaluate() in the mapping table load as shown in my script.

edit: There are multiple ways to create the mapping table, if you don't like the Evaluate(), you can for example do it like

FieldMap: 

MAPPING 

LOAD 'v'&RecNo() as In,

Date(Monthstart(Today(),-(Recno()-1)),'YYYY-MM') as Out

AutoGenerate 10;

chulasantos
New Contributor

Re: Date issue (string to year-month)

When I use Evaluate, gives me the error:

"Field 'Out' not found"

Highlighted
chulasantos
New Contributor

Re: Date issue (string to year-month)

FieldMap: 

MAPPING 

LOAD 'v'&RecNo() as In,

Date(Monthstart(Today(),-(Recno()-1)),'YYYY-MM') as Out

AutoGenerate 10;

Why autogenerate 10?

MVP
MVP

Re: Date issue (string to year-month)

Autogenerate 10 will create 10 records in your mapping table.

You can adapt the number of records to the number of v1, v2, v3 ... fields in your table.

MVP
MVP

Re: Date issue (string to year-month)

Joao Santos wrote:

When I use Evaluate, gives me the error:

"Field 'Out' not found"

Could you post your current script?

'Out' should be a field from the MAPPING LOAD INLINE table.

chulasantos
New Contributor

Re: Date issue (string to year-month)

FieldMap: 

MAPPING 

LOAD In, Evaluate(Out) as Out INLINE [

In, Out 

v1, "Date(Monthstart(Today(),0),'YYYY-MM')" 

v2, "Date(Monthstart(Today(),-1),'YYYY-MM')" 

v3, "Date(Monthstart(Today(),-2),'YYYY-MM')" 

v4, "Date(Monthstart(Today(),-3),'YYYY-MM')"

v5, "Date(Monthstart(Today(),-4),'YYYY-MM')"

v6, "Date(Monthstart(Today(),-5),'YYYY-MM')"

v7, "Date(Monthstart(Today(),-6),'YYYY-MM')"

v8, "Date(Monthstart(Today(),-7),'YYYY-MM')"

v9, "Date(Monthstart(Today(),-8),'YYYY-MM')"

v10, "Date(Monthstart(Today(),-9),'YYYY-MM')"

v11, "Date(Monthstart(Today(),-10),'YYYY-MM')"

v12, "Date(Monthstart(Today(),-11),'YYYY-MM')"

v13, "Date(Monthstart(Today(),-12),'YYYY-MM')"

]; 

TMP:

CrossTable(Month,Sales_Unit,3)

LOAD

    [Store],

    [Product],

    v1,

    v2,

    v3,

    v4,

    v5,

    v6,

    v7,

    v8,

    v9,

    v10,

    v11,

    v12,

    v13

FROM [lib://AttachedFiles/Sellout_datafile.txt]

(txt, codepage is 28592, embedded labels, delimiter is '\t', msq);

Sellout:

NoConcatenate

Load [Store],

[Product],

ApplyMap('FieldMap',Month,'Missing map for: '& Month) as Month,

Sales_Unit

Resident TMP;

Drop Table TMP;

MVP
MVP

Re: Date issue (string to year-month)

Worked just fine for me (using your script with an INLINE sample record for table TMP).

The mapping table load showed no error.

chulasantos
New Contributor

Re: Date issue (string to year-month)

I use qlikcloud / qliksense..., could you share the file in this format?

MVP
MVP

Re: Date issue (string to year-month)

In my last answer, I've attached a Qliksense file that you can open with QlikSense Desktop.

But essentially, it's just the script that you've posted above.

edit: Just imported the app into QlikSense Cloud. Same result, no issues.

2018-08-31 17_12_03-comm310735.png

balabhaskarqlik
Honored Contributor

Re: Date issue (string to year-month)

May be refer this:

Month number to Month name