Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chan103148
Contributor III
Contributor III

ApplyMap to selected date range only

good day,

I am from fashion retail.

I would like to ask if it is possible to use ApplyMap function within a selected range of date only because I have a field called Item type, this item type is like a tag if a garment is on a regular price or on Markdown. 

for example:

Jan 2019 item 1 is tagged as REG

.

.

Nov 2019 item 1 is tagged as MD

 

understanding ApplyMap function, on Nov 2019 item 1 is already MD so previous months (Jan - Oct) it will be also tagged as MD. I want to freeze those previous data before MD.

 

I hope someone would help me

 

thank you.

Labels (2)
2 Solutions

Accepted Solutions
kaushiknsolanki

Hi,

Find the script Below.

Map1:
Mapping
LOAD Num(Date)&'-'&ItemCode as X,
ItemName as Y
FROM
[C:\Users\Kaushik\Downloads\TestData.xlsx]
(ooxml, embedded labels, table is Mapping);

Transactions:
LOAD Date,
ItemCode,
Value,
ItemName,
ApplyMap('Map1',Num(Date)&'-'&ItemCode) as ItemName_New
FROM
[C:\Users\Kaushik\Downloads\TestData.xlsx]
(ooxml, embedded labels, table is Transactions);

 

The data excel is attached here with.

 

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

kaushiknsolanki

Hi, If your master file or mapping file doesnt have date field, how do you decide that till which date it was X value and then Y value.

There has to be some way to identify when mapping is changed, so you need to have atleast a start and end date in master file.

Hope you got my point.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

5 Replies
kaushiknsolanki

Hi,

 

You can create the Mapping field as composite key, that would have the date (Or the month, which ever is the case) and the Item Code. Like in your example the mapping table data should look like below.

Field1, Field2

Jan 2019-1, REG

Nov 2019-1, MD

 

So that you get correct  mapping for correct date.

 

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
chan103148
Contributor III
Contributor III
Author

hi,

 

can you give me sample script, im just a beginner in using qlik.

 

thank you

kaushiknsolanki

Hi,

Find the script Below.

Map1:
Mapping
LOAD Num(Date)&'-'&ItemCode as X,
ItemName as Y
FROM
[C:\Users\Kaushik\Downloads\TestData.xlsx]
(ooxml, embedded labels, table is Mapping);

Transactions:
LOAD Date,
ItemCode,
Value,
ItemName,
ApplyMap('Map1',Num(Date)&'-'&ItemCode) as ItemName_New
FROM
[C:\Users\Kaushik\Downloads\TestData.xlsx]
(ooxml, embedded labels, table is Transactions);

 

The data excel is attached here with.

 

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

chan103148
Contributor III
Contributor III
Author

hi with this example,

I guess I need to put date on my Masterfile (mapping table) and have to be the same with my transaction data. I think it won't work with my data.

I can't put a date on my master file that is exactly the same with transaction details.

is it possible to apply mapping with set analysis? so that I can select a date range instead.

 

thank you

kaushiknsolanki

Hi, If your master file or mapping file doesnt have date field, how do you decide that till which date it was X value and then Y value.

There has to be some way to identify when mapping is changed, so you need to have atleast a start and end date in master file.

Hope you got my point.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post