topic Re: ApplyMap to selected date range only in Connectivity & Data Prep

ApplyMap to selected date range only

chan103148 — Fri, 10 Dec 2021 20:29:42 GMT

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.

Re: ApplyMap to selected date range only

kaushiknsolanki — Fri, 13 Dec 2019 04:23:38 GMT

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

Re: ApplyMap to selected date range only

chan103148 — Fri, 13 Dec 2019 07:55:21 GMT

hi,

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

thank you

Re: ApplyMap to selected date range only

kaushiknsolanki — Tue, 17 Dec 2019 06:32:57 GMT

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

Re: ApplyMap to selected date range only

chan103148 — Thu, 19 Dec 2019 01:33:28 GMT

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

Re: ApplyMap to selected date range only

kaushiknsolanki — Thu, 19 Dec 2019 06:36:34 GMT

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