Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chulasantos
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
swuehl
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
sunny_talwar

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

chulasantos
Contributor
Contributor
Author

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

sunny_talwar

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...

chulasantos
Contributor
Contributor
Author

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

sunny_talwar

May be like this

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

manoj217
Creator III
Creator III

you want to rename your fields with a date format?

chulasantos
Contributor
Contributor
Author

‌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.

swuehl
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;

chulasantos
Contributor
Contributor
Author

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!