topic Re: Mapping Load in QlikView

Mapping Load

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can some one help me here for mapping load and apply map

i am writing script as below,

MAP:

mapping LOAD

[PSA Project pse Project ID],
[HOLD Closure date2]

FROM
[$(QVD_Path)\GSD_HOLDClosuredate2.qvd]
(qvd);

HOLD:

LOAD

     [PSA Project pse Project ID],
     [HOLD Closure date],
    Applymap('MAP', [PSA Project pse Project ID],[HOLD Closure date2]) as T

FROM
[$(QVD_Path)\GSD_HOLDClosuredate.qvd]
(qvd);

out put i got as below,

but here one Project ID can have multiple dates, but when i wrote apply map for "T" column 2 rows dates are coming same which it should not be. it should come one date as 08/16/2017 and second date as 09/29/2017. as shown in second snapshot.

can some one help me here.

Re: Mapping Load

prma7799 — Thu, 02 Aug 2018 09:07:16 GMT

try this

Applymap('MAP', [PSA Project pse Project ID], Null() ) as T

or please share some sample app

* you have two records ([HOLD Closure date]) for [PSA Project pse Project ID] (PR- 539478)

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 10:21:28 GMT

HI,

I already Tried that same results i am getting

in T Column we are getting same Date's but i should see 2 different Dates.

Re: Mapping Load

peterwh — Thu, 02 Aug 2018 10:39:00 GMT

Hello,

mapping doesn't work with multiple values under one key ([PSA Project pse Project ID]). So this is the normal behaviour.

"The only time that you cannot use Applymap() instead of a join is if you want to fetch more than one single corresponding value (record) from the second table." (Source: https://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap)

Kind regards

Peter

Re: Mapping Load

mhappiee — Thu, 02 Aug 2018 12:08:19 GMT

Any specific reason to use Mapping load ?,

if not you can simply do that using joins like left or Inner

Anand

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 12:49:41 GMT

How about this

MAP:

MAPPING

LOAD [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID]),

[HOLD Closure date2]
FROM [$(QVD_Path)\GSD_HOLDClosuredate2.qvd] (qvd);

HOLD:

LOAD [PSA Project pse Project ID],
[HOLD Closure date],
Applymap('MAP', [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID])) as T

FROM [$(QVD_Path)\GSD_HOLDClosuredate.qvd] (qvd);

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 12:55:02 GMT

Hi Anand,

i tried using join it is not working as it will duplicates the rows.

As we have 2 timestamps for same Project ID.

Re: Mapping Load

BalaBhaskar_Qlik — Thu, 02 Aug 2018 12:55:57 GMT

May be like this:

MAP:

Mapping LOAD

[PSA Project pse Project ID],

[HOLD Closure date2]

FROM

[$(QVD_Path)\GSD_HOLDClosuredate2.qvd](qvd);

HOLD:

LOAD

[PSA Project pse Project ID],

[HOLD Closure date],

Applymap('MAP', [PSA Project pse Project ID], ApplyMap('MAP',[HOLD Closure date2],Null()) as T

FROM

[$(QVD_Path)\GSD_HOLDClosuredate.qvd](qvd);

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 12:57:12 GMT

Hi Sunny,

Thank You for looking into this.

i tried your script below is what i got , i am getting project iD's instead of time stamps.

but i need below 2 time stamps

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 12:59:44 GMT

How many instances of PR-539478 do you have in your GSD_HOLDClosuredate.qvd? It seems that it might be more than 4? how many instances do you have in GSD_HOLDClosuredate2.qvd?

If one qvd has more than the other, how do you expect to map them?

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 13:48:51 GMT

Hi Sunny,

For Project ID PR-539478 have 2 Date instances in GSD_HOLDClosuredate.qvd

and Project ID PR-539478 have 2 Date instances in GSD_HOLDClosuredate2.qvd

one project id can have multiple date fields

like from above example i am taking HOLD Closure Date where old value text =Hold.

HOLD Closure Date 2 where New Value Text =HOLD

Date Fields is History Time stamp in above snapshot.

basically i want to calculate days of old value text =Hold Time stamp - New Value Text =HOLD Time stamp.

for that to calculate in days i need it in 2 rows instead of 4 rows.

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 14:16:55 GMT

Can you share the script used to create the two qvds?

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 14:20:44 GMT

Hi Sunny,

GSDHOLDClosuredate:

LOAD

    PSA_Project_Internal_Id as [Project ID],


    (Date(Floor( [Created_Timestamp]),'MM/DD/YYYY')) as [HOLD Closure date],

    '' as [HOLD Closure date2]

//    Field_Text,
//    Old_Value_Text,
//    New_Value_Text

FROM
[$(ITG_QVD_Path)\GSD_PSA_Project_History.qvd]
(qvd)
where (Field_Text= 'pse__Project_Status__c' AND (Old_Value_Text='On Hold'))

;

INNER JOIN

LOAD

     [PSA_Project_Internal_Id] as [Project ID],
     [PSA Project pse Project ID]

FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND [PSA Project pse Project Status Text] <> '#'

;

STORE GSDHOLDClosuredate into [$(QVD_Path)\GSD_HOLDClosuredate.qvd];

Drop table GSDHOLDClosuredate;

GSDHOLDClosuredate2:

LOAD

    PSA_Project_Internal_Id as [Project ID],

    '' as [HOLD Closure date],


    (Date(Floor( [Created_Timestamp]),'MM/DD/YYYY')) as [HOLD Closure date2]
//
//    Field_Text,
//    Old_Value_Text,
//    New_Value_Text

FROM
[$(ITG_QVD_Path)\GSD_PSA_Project_History.qvd]
(qvd)
where (Field_Text= 'pse__Project_Status__c' AND (New_Value_Text='On Hold'))

;

INNER JOIN

LOAD

     [PSA_Project_Internal_Id] as [Project ID],
     [PSA Project pse Project ID]

FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND [PSA Project pse Project Status Text] <> '#'

;

STORE GSDHOLDClosuredate2 into [$(QVD_Path)\GSD_HOLDClosuredate2.qvd];

Drop table GSDHOLDClosuredate2;

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 14:28:57 GMT

Could the inner join be causing multiplication of data in your qvd file?

Can you run the INNER JOIN like this and see if duplication is avoided?

INNER JOIN

LOAD DISTINCT

    [PSA_Project_Internal_Id] as [Project ID],
    [PSA Project pse Project ID]

FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND [PSA Project pse Project Status Text] <> '#'

;

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 14:39:36 GMT

Hi Sunny,

No luck , still its same.

Basically below logic gives distinct values only with out Distinct also.

LOAD

    [PSA_Project_Internal_Id] as [Project ID],
    [PSA Project pse Project ID]

FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND [PSA Project pse Project Status Text] <> '#'

>>>>>>>>>>>>>>>>>>>>>>>>>>> But in History object one project has 4 Time stamps

2 time stamps with Old value Text=HOLD

2 time stamps with New value Text =HOLD

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 14:45:58 GMT

Would it be possible for you to share your GSD_HOLDClosuredate.qvd?

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 14:51:00 GMT

Hi Sunny,

I attached in the original Post.

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 15:20:29 GMT

I think I might know what went wrong... can you try this

MAP:

MAPPING

LOAD [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 1),

[HOLD Closure date2]
FROM [$(QVD_Path)\GSD_HOLDClosuredate2.qvd] (qvd);

HOLD:

LOAD [PSA Project pse Project ID],
[HOLD Closure date],
Applymap('MAP', [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 2)) as T

FROM [$(QVD_Path)\GSD_HOLDClosuredate.qvd] (qvd);

Re: Mapping Load

sunny_talwar — Thu, 02 Aug 2018 15:38:03 GMT

This seems to work

MAP:

MAPPING

LOAD [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 1),

[HOLD Closure date2]

FROM [..\..\Downloads\GSD_HOLDClosuredate2.qvd] (qvd);

HOLD:

LOAD [PSA Project pse Project ID],

[HOLD Closure date],

Applymap('MAP', [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 2), Null()) as T

FROM [..\..\Downloads\GSD_HOLDClosuredate.qvd] (qvd);

But make sure to sort the data in ascending or descending order before you create the qvds

Re: Mapping Load

dunnalahk123 — Thu, 02 Aug 2018 15:38:33 GMT

HI Sunny,

Yes it works. only one thing i noticed is dates are coming in not order.

But I guess it Ok bec i need the final output in days. which is matching .