topic Re: Mapping the two table with condition. in QlikView

Mapping the two table with condition.

iahmadmca1 — Tue, 11 Jul 2017 04:19:23 GMT

Team,

just I want to take the small help regarding the map two table with the condition.Pease finds the below code which I am using.and correct me.

Test:
Mapping
LOAD
[PCAD Job Number],
[Issuance Type]

FROM

(qvd)
where [Issuance Type]='Scrub - OWM' or [Issuance Type]='Scrub - Rev Pro';

Scrub:
LOAD issuanceTypeId,
issuanceDescription,
Applymap('Test',issuanceDescription,Null()) as Issuance_Type,
workPlanDescription,
YellowSLA,
RedOperator,
RedSLA
FROM

(qvd);

Based on PCAD number I am mapping with two table and filtering the record based on [Issuance Type].

whatever data will filter after test table that should be mapped with second table scrub.Please check and give me some idea.

Re: Mapping the two table with condition.

Anonymous — Tue, 11 Jul 2017 05:44:46 GMT

Hello Ahmad

Please share sample data to work on this

Re: Mapping the two table with condition.

iahmadmca1 — Tue, 11 Jul 2017 05:52:36 GMT

okay, Mahesh let me share.

Re: Mapping the two table with condition.

iahmadmca1 — Tue, 11 Jul 2017 06:15:11 GMT

Mahesh,

Please find the below sample data with I need to map with each other.

based on this two table I need a result.

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

Anonymous — Tue, 11 Jul 2017 06:31:44 GMT

HI Ahmed

Can you Please share two tables data in excel , so that it could be easy for me to work on this .

And also can you share accepted output .

Thanks

Mahesh.

Re: Mapping the two table with condition.

balar025 — Tue, 11 Jul 2017 06:41:41 GMT

Hi Irshad,

Code you have written will always give NULL() as Issuance Type. Because in mapping table , first column is always Key and second is value. So,

Applymap('Test',issuanceDescription,Null()) this line of code will check issuanceDescription in [PCAD Job Number] column, which will never match. And end result will be always NULL().

This might help you.

Regards,

Ravi

Re: Mapping the two table with condition.

Peter_Cammaert — Tue, 11 Jul 2017 06:42:29 GMT

It appears that field issuanceDescription of table scrub contains the same values as the output of the Mapping Table. Mapping tables cannot be used in two directions, for example to translate the second mapping table column into the first one. So the applymap call in your second load is not entirely correct.

Moreover, from your description it isn't clear what you are trying to accomplish. Can you give an example of your input data (both tables) and what the result of the transformation should be? Remember that QlikView is unable to parse data from screenshots. It would be easier if you could supply the data in csv or xlsx format.

Re: Mapping the two table with condition.

iahmadmca1 — Tue, 11 Jul 2017 06:56:56 GMT

Thanks, Ravi.

Re: Mapping the two table with condition.

iahmadmca1 — Tue, 11 Jul 2017 07:11:39 GMT

Hi Team,

I attached the sample Excel data for your reference.please check it and let me know.

sample output which I am expecting:

Issuance Type	Pcad Number	Active Account	Order status
Scrub - OWM	DSOET-20170418-00081	Active	Pending
Scrub - Rev Pro	DSOET-20170420-00285	Active	pending
Scrub - OWM	DSOET-20170708-00018	Active	pending
Scrub - Rev Pro	F_MS-20170620-02821	InActive	pending

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

balar025 — Tue, 11 Jul 2017 11:40:48 GMT

Hi Irshad,

What is your expectation because i am not able to visualize your output?

Because as per your latest reply, i am seeing multiple PCAD Number against single Issuance Type. How your checking that and what is Active account and Order Status?

Because by looking at data. It looks insufficient.

********All you PCAD number is assigned to same issuance type************

DSOET-20170418-00081	Scrub - OWM	1
DSOET-20170420-00285	Scrub - OWM	1
DSOET-20170708-00018	Scrub - OWM	1
F_MS-20170620-02821	Scrub - OWM	1

Regards,

Ravi Balar

Re: Mapping the two table with condition.

iahmadmca1 — Wed, 12 Jul 2017 03:02:33 GMT

Team,

sorry for the late reply, Please finds the attached excel sheet for sample output and let me any clarification.

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

iahmadmca1 — Wed, 12 Jul 2017 04:55:16 GMT

Ravi,

I want to Map the Test and scrub table based on below condition.

where [Issuance Type]='Scrub - OWM' or [Issuance Type]='Scrub - Rev Pro';

Sample output I have attached in the loop, Please let me know in case of any clarification.

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

mangalsk — Wed, 12 Jul 2017 09:03:56 GMT

Check this

test:

Mapping

LOAD Distinct

[Issuance Type],

[PCAD Job Number]

FROM

(qvd)
where [Issuance Type]='Scrub - OWM' or [Issuance Type]='Scrub - Rev Pro';

scrub:

LOAD *,

ApplyMap('test',[Issuance Type]) as [PCAD Job Number];

LOAD

issuanceTypeId,

issuanceDescription as [Issuance Type],

//ApplyMap([Issuance Type],issuanceDescription,'test') as t,

workPlanDescription,

YellowSLA,

RedOperator,

RedSLA

FROM

(qvd);

Re: Mapping the two table with condition.

iahmadmca1 — Thu, 13 Jul 2017 00:51:43 GMT

Thanks Mangal,

I will check and let you know.

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

iahmadmca1 — Thu, 13 Jul 2017 02:12:54 GMT

Hi Mangal,

I have checked your code its working fine but I could see there is showing only two records.Please find the below screenshots for your reference.

key point:

1. In test table, I have 42 issuance type and PCAD number

2. Scrub table, i have 2 issuances type only no PCAD number

I need both table record as an output with the map.Please find the below output.

Thanks,

Irshad Ahmad

Re: Mapping the two table with condition.

mangalsk — Thu, 13 Jul 2017 09:31:39 GMT

For this instead you should join as follows

test:

//Mapping

LOAD Distinct

[Issuance Type],

[PCAD Job Number]

FROM

(biff, embedded labels, table is Sheet1$);//where [Issuance Type]='Scrub - OWM' or [Issuance Type]='Scrub - Rev Pro';

Join

scrub:

//LOAD *,

//ApplyMap('test',[Issuance Type]) as [PCAD Job Number];

LOAD

issuanceTypeId,

issuanceDescription as [Issuance Type],

//ApplyMap([Issuance Type],issuanceDescription,'test') as t,

workPlanDescription,

YellowSLA,

RedOperator,

RedSLA

FROM

(biff, embedded labels, table is Sheet1$);

Re: Mapping the two table with condition.

iahmadmca1 — Fri, 14 Jul 2017 00:27:08 GMT

Thanks, Mangal