Apply map or Lookup to fill absent client data based on Order Numbers?

[#1]

Currently I am facing this situation.

We have a table which consist of data of several subsystem.

For example. AP, AR, GL, etc

Due to an error in the deployment of those subsystems, customer information deviates from the whole for two specific countries.

More specifically, for two countries both [Sold to]  en [Bill to] customer information is absent in the AR system. Therefore, in order to fill both [Sold to] en [Bill to] information fields it should look in the AP side of the table as this data is available here. It should look to the AP entries of the table and find the information here based on Order Number.

Theoretically this sounds quite simple. However I am struggling to implement this in QlikView in a fixed routine. Which option are there? I do have an idea myself but I doubt this is working. I am looking for a robust/performance-friendly solution as this solution concerns large volumes of data (+/- 70gb). I put an illustration of the situation below.

Thank in advance!

Year	Period#	Country	Subsystem	Order#	Bill to#	BilltoName	Sold to#	SoldtoName
2013	4	GB	AP	42	194491	XXX	79850	XXX
2013	4	GB	AP	27	79193	YYY	480021	YYY
2013	4	GB	AP	10	115565	ZZZ	84302	ZZZ
2013	4	GB	AP	23	101928	AAA	73695	AAA
2013	4	GB	AP	43	128635	CCC	332010	CCC
2013	4	GB	AP	23	170041	MMM	81492	MMM
2013	4	GB	AR	42
2013	4	GB	AR	27
2013	4	GB	AR	10
2013	4	GB	AR	23
2013	4	GB	AR	43
2013	4	GB	AR	23
2013	4	GB	FA	43	76136	XXX	89034	XXX
2013	4	GB	FA	11	63194	YYY	480021	YYY
2013	4	GB	FA	19	103103	WWW	109260	ZZZ
2013	4	GB	FA	27	35644	AAA	85998	AAA
2013	4	GB	FA	32	14880	BBB	332010	CCC
2013	4	GB	FA	11	123891	MMM	168557	MMM

[#2 · Accepted solution]

Hi,

Try this

Map1:

Mapping Load * Inline [
Order#, Bill to#
42, 194491
27, 79193
10, 115565
23, 101928
43, 128635
23, 170041
];

Map2:
Mapping Load * Inline [
Order#, Sold to#
42, 79850
27, 480021
10, 84302
23, 73695
43, 332010
23, 81492
];

Test2:
Load * Inline [
Year, Period#, Country, Subsystem, Order#, Bill to#, BilltoName, Sold to#, SoldtoName
2013, 4, GB, AP, 42, 194491, XXX, 79850, XXX
2013, 4, GB, AP, 27, 79193, YYY, 480021, YYY
2013, 4, GB, AP, 10, 115565, ZZZ, 84302, ZZZ
2013, 4, GB, AP, 23, 101928, AAA, 73695, AAA
2013, 4, GB, AP, 43, 128635, CCC, 332010, CCC
2013, 4, GB, AP, 23, 170041, MMM, 81492, MMM
2013, 4, GB, AR, 42, , , ,
2013, 4, GB, AR, 27, , , ,
2013, 4, GB, AR, 10, , , ,
2013, 4, GB, AR, 23, , , ,
2013, 4, GB, AR, 43, , , ,
2013, 4, GB, AR, 23, , , ,
2013, 4, GB, FA, 43, 76136, XXX, 89034, XXX
2013, 4, GB, FA, 11, 63194, YYY, 480021, YYY
2013, 4, GB, FA, 19, 103103, WWW, 109260, ZZZ
2013, 4, GB, FA, 27, 35644, AAA, 85998, AAA
2013, 4, GB, FA, 32, 14880, BBB, 332010, CCC
2013, 4, GB, FA, 11, 123891, MMM, 168557, MMM
];

ResTest2:

ResTest2:

Load Year,Period#,Country,Subsystem,Order#,BilltoName,SoldtoName,
If(Subsystem<>'FA',ApplyMap('Map1',Order#),[Bill to#]) as [Bill To#],
If(Subsystem<>'FA',ApplyMap('Map2',Order#),[Sold to#]) as [Sold to#]
Resident Test2;

Drop Table Test2;

[jonathandienst]

Hi

The way to do this normally would be to create two mapping tables after loading the AP data:

Map_BillTo_No:

Mapping LOAD Distinct [Order#]

     [BillTo#]

Resident Data

Where Subsystem = 'AP'

;

Map_BillTo_Name:

Mapping LOAD Distinct [Order#]

     [BillToName]

Resident Data

Where Subsystem = 'AP'

;

And do the same for SoldTo.

Now

     Left Join(Data)

     LOAD [Order#],

          Year,

          Period,

          Country

          'AR' As Subsystem,

          If(IsNull([BillTo#]), ApplyMap('Map_BillTo_No', [Order#], Null()), [BillTo#]) As [BillTo#],

          If(IsNull([BillToName]), ApplyMap('Map_BillTo_Name', [Order#], Null()), [BillToName]) As [BillToName],

          ... and do the same for sold to

     Resident Data;

As usual for such a join you must make sure that there are no duplicates using all the parameters (Order# to Country). If not, you will need to include more parameters until the LOAD returns distinct values.

If you load AP system first, then you can use the ApplyMaps during the AR system load and there is no need for a join.

HTH

Jonathan

[#4]

Hi guys,

Thank you for replying.

Great to see I was thinking in the same direction.

My solution will be a bit more complex but I can use both examples raised as a basis.

I only worry about the performance when reloading the script as huge amounts of data are involved.

@ Jonathan:

I would rather prevent joins at this point. The dataquality is not always very consistent throughout the time and i think there is a high chance of duplicate records. The existence of duplicates went wrong before as this was creating a lot extra records after the join. It took me hours to detect what actually wrong. So i would rather choose only for the applymap().

But thanks again. It is working fine for now!

[jonathandienst]

If you load by susbsystem, then load AP first, then build the mapping, then load AR and apply the mapping during the load. This way you are not reloading any data, nor using any joins, so I would expect to be the best possible performance to fill in those missing fields.

Jonathan