Qlik Community

Qlik Sense Cloud Discussions

Highlighted
chulasantos
New Contributor

Data mapping with reference month

Hello!

For each row, i have a column in a data txt file with a string that means a reference date of the other columns of sales data (sRef_YearMonth )
In the first row, the sRefDate is Aug 2018, the V1 column is the same of Ref Date, the V2 is Jul 2018 (RefDate-1), the V3 column is Jun 2018 (RefDate-2), ...

data txt  example:

store           product               sRef_YearMonth                 v1 v2 v3 v4 ... v14
A                       123                         201808                               6    3    4   5        2
B                       123                         201806                               2    4    6   7        1


My strategy is:
1. load data applying the crosstable:

TMP:
CrossTable (YearMonth, Sales_Unit, 3)
Load
store,
product,
sRef_YearMonth,
v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14
from [lib://selldata.txt]
(txt, codepage is 28592, embedded labels, delimiter is '\t', msq);

2. ApplyMap

in the example the map results from the today's date. I want apply map in function of table column "sRefMonth".

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')"
v14, "Date(Monthstart(Today(),-13),'YYYY-MM')"
];

3. Final Sellout Data table:
Load
Store, product,
ApplyMap('FieldMap',YearMonth,'Missing map for: '& Month) as Year_Month,   
Sales_Unit
Resident TMP;    
DROP TABLE TMP; 


note:
i've tried to use in mapping step this function this:

MAPPING 
LOAD In, Evaluate(Out) as Out INLINE [
In, Out 

v1, "date(MonthStart(sRef_YearMonth,0),'YYYY-MM')
v2, "date(MonthStart(sRef_YearMonth,-1),'YYYY-MM')
v3, "date(MonthStart(sRef_YearMonth,-2),'YYYY-MM')
...

But didn´t work!! 😞

Can you help?

5 Replies
MVP & Luminary
MVP & Luminary

Re: Data mapping with reference month

Try this mapping table:

 

FieldMap: 
MAPPING LOAD
'v' & RecNo() as In,
Date(Monthstart(Today(),1-RecNo()),'YYYY-MM') as Out
AutoGenerate 14
;

 


talk is cheap, supply exceeds demand
chulasantos
New Contributor

Re: Data mapping with reference month

Hi,

thank you for your reply, but i dont want use  today as reference.

I want use the column "sRef_YearMonth"

 

Thanks

MVP & Luminary
MVP & Luminary

Re: Data mapping with reference month

You can't use a mapping table for that. Try this instead: 

Load
    Store, 
    product,
    Date(MonthStart(sRef_YearMonth, 1-Mid(YearMonth,2)),'YYYYMM') as Year_Month,   
    Sales_Unit
Resident 
    TMP
;      

 


talk is cheap, supply exceeds demand
chulasantos
New Contributor

Re: Data mapping with reference month

Thanks for your reply but it didn´t work...

this is the result:

 

v1 = 201808 and not 245109 ...

Screenshot_3.png

MVP & Luminary
MVP & Luminary

Re: Data mapping with reference month

Then your sRef_YearMonth field does not contain dates but just plain numbers. You can't use the Monthstart field on those numbers. You'll have to convert the numbers to dates first to be able to use date functions on them. You can use the Date#() function for that: Date(Date#(sRef_YearMonth, 'YYYYMMDD'),'YYYYMMDD') as sRef_YearMonth. Once you've done that the sRef_YearMonth field will contain date values and you can use the MonthStart function on it.


talk is cheap, supply exceeds demand