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

[swuehl]

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;

[chulasantos]

When I use Evaluate, gives me the error:

"Field 'Out' not found"

[chulasantos]

FieldMap: 

MAPPING 

LOAD 'v'&RecNo() as In,

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

AutoGenerate 10;

Why autogenerate 10?

[swuehl]

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.

[swuehl]

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.

[chulasantos]

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;

[swuehl]

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

The mapping table load showed no error.

[chulasantos]

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

[swuehl · Accepted solution]

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.

[balabhaskarqlik]

May be refer this:

Month number to Month name