topic Re: Date issue (string to year-month) in App Development

Date issue (string to year-month)

chulasantos — Fri, 10 Aug 2018 15:06:36 GMT

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

Re: Date issue (string to year-month)

sunny_talwar — Fri, 10 Aug 2018 16:19:45 GMT

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

Re: Date issue (string to year-month)

chulasantos — Fri, 10 Aug 2018 16:39:39 GMT

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

Re: Date issue (string to year-month)

sunny_talwar — Fri, 10 Aug 2018 16:47:58 GMT

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

Re: Date issue (string to year-month)

chulasantos — Fri, 10 Aug 2018 16:53:55 GMT

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

Re: Date issue (string to year-month)

sunny_talwar — Fri, 10 Aug 2018 17:10:14 GMT

May be like this

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

Re: Date issue (string to year-month)

manoj217 — Fri, 17 Aug 2018 13:01:19 GMT

you want to rename your fields with a date format?

Re: Date issue (string to year-month)

chulasantos — Fri, 17 Aug 2018 23:41:59 GMT

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

Re: Date issue (string to year-month)

swuehl — Sat, 18 Aug 2018 09:57:26 GMT

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;

Re: Date issue (string to year-month)

chulasantos — Wed, 29 Aug 2018 16:16:08 GMT

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,

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!

Re: Date issue (string to year-month)

swuehl — Wed, 29 Aug 2018 20:05:59 GMT

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;

Re: Date issue (string to year-month)

chulasantos — Thu, 30 Aug 2018 11:43:32 GMT

When I use Evaluate, gives me the error:

"Field 'Out' not found"

Re: Date issue (string to year-month)

chulasantos — Thu, 30 Aug 2018 12:03:35 GMT

FieldMap:

MAPPING

LOAD 'v'&RecNo() as In,

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

AutoGenerate 10;

Why autogenerate 10?

Re: Date issue (string to year-month)

swuehl — Thu, 30 Aug 2018 12:21:39 GMT

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.

Re: Date issue (string to year-month)

swuehl — Thu, 30 Aug 2018 15:30:32 GMT

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.

Re: Date issue (string to year-month)

chulasantos — Fri, 31 Aug 2018 11:08:17 GMT

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;

Re: Date issue (string to year-month)

swuehl — Fri, 31 Aug 2018 14:10:44 GMT

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

The mapping table load showed no error.

Re: Date issue (string to year-month)

chulasantos — Fri, 31 Aug 2018 14:59:04 GMT

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

Re: Date issue (string to year-month)

swuehl — Fri, 31 Aug 2018 15:03:25 GMT

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.

Re: Date issue (string to year-month)

BalaBhaskar_Qlik — Sat, 01 Sep 2018 09:46:17 GMT

May be refer this:

Month number to Month name