Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 (1)
  • SaaS

2 Solutions

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
chan103148
Contributor III
Contributor III
Author

hi,

 

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

 

thank you

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!