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

1 Solution

Accepted Solutions
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

19 Replies
Highlighted

Use The Crosstable Load to transform your table and then use a resident load to format your field as you like

Highlighted
Contributor
Contributor

But how can I convert strings (v1,v2,v3,... ) to year and months ?

Highlighted

Well, I am not sure how you will get year from v1, but if 1 in v1 means Jan, then you can def. extract Month from it...

Highlighted
Contributor
Contributor

v1 means today month, v2 means Month-1, v3 month-2,...

Highlighted

May be like this

Date(MonthStart(Today(), Num#(PurgeChar(FieldName, 'v')) - 1), 'YYYY-MM') as YearMonth

Highlighted
Creator III
Creator III

you want to rename your fields with a date format?

Highlighted
Contributor
Contributor

‌yes. I want convert “v”s to month and year-months.

For example today v1 is august, v2 is july, v3 is june, ...

the ”v”s are moving months.

Highlighted
MVP
MVP

You can either rename the fields using a mapping table:

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')"

];

Facts:

LOAD * INLINE [

Store,      Product,            v1,    v2,    v3

StoreA,      product1,        12,      15,    7

StoreA,      product2,          8,      11,    16

StoreB,      product1,          6,      8,      5

StoreC,      product2,          3,      5,      4

];

Rename Fields using FieldMap;

Or, and that resulting data model would be advisable for further data analysis using Qlik, use a CROSSTABLE LOAD as already suggested and map the field values:

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')"

];

TMP:

CROSSTABLE (Month,Sales,2)

LOAD * INLINE [

Store,      Product,            v1,     v2,     v3

StoreA,      product1,         12,      15,     7

StoreA,      product2,           8,      11,     16

StoreB,      product1,           6,      8,       5

StoreC,      product2,           3,      5,       4

];

Facts:

NoConcatenate

LOAD Store, Product,

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

Sales

Resident TMP; 

DROP TABLE TMP;

Highlighted
Contributor
Contributor

Hi,

Thank you for you response, but when I'm loading the script and after loading the crosstable it gives a wrong data table:

The month column gives a string and and not the year-month.

Script:

FieldMap: 

MAPPING 

LOAD * 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')" 

]; 

TMP:

CrossTable(Month,Sales_Unit,3)

LOAD

    [Número ANF] as [Store],

    Código as Product,

    v1,

    v2,

    v3,

    v4

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;

Thanks for your help!