Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Date issue (string to year-month)

Hi,

I have a table with data sales :

Colum header: Store      Product            v1     v2     v3 ...


Rows:               StoreA      product1         12      15     7


                         StoreA      product2           8      11     16


                         StoreB      product1           6      8       5


                         StoreC      product2           3      5       4                  


.....

Where v1, v2, v3, are the month, month -1 and month -2 sales ....

How can i convert  the headers v1, v2, v3 to Year Month like YYYY-MM?

and then convert it to a cross table with:

Store, Year Month, Product, Sales

Thanks

19 Replies
Highlighted
MVP
MVP

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;

Highlighted
Contributor
Contributor

When I use Evaluate, gives me the error:

"Field 'Out' not found"

Highlighted
Contributor
Contributor

FieldMap: 

MAPPING 

LOAD 'v'&RecNo() as In,

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

AutoGenerate 10;

Why autogenerate 10?

Highlighted
MVP
MVP

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.

Highlighted
MVP
MVP

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.

Highlighted
Contributor
Contributor

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;

Highlighted
MVP
MVP

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

The mapping table load showed no error.

Highlighted
Contributor
Contributor

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

Highlighted
MVP
MVP

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

View solution in original post

Highlighted

May be refer this:

Month number to Month name