Date issue (string to year-month)

[chulasantos]

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

[sunny_talwar]

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

[chulasantos]

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]

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]

you want to rename your fields with a date format?

[chulasantos]

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

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]

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!