Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

YearPeriod#CountrySubsystemOrder#Bill to#BilltoNameSold to#SoldtoName
20134GBAP42194491XXX79850XXX
20134GBAP2779193YYY480021YYY
20134GBAP10115565ZZZ84302ZZZ
20134GBAP23101928AAA73695AAA
20134GBAP43128635CCC332010CCC
20134GBAP23170041MMM81492MMM
20134GBAR42
20134GBAR27
20134GBAR10
20134GBAR23
20134GBAR43
20134GBAR23
20134GBFA4376136XXX89034XXX
20134GBFA1163194YYY480021YYY
20134GBFA19103103WWW109260ZZZ
20134GBFA2735644AAA85998AAA
20134GBFA3214880BBB332010CCC
20134GBFA11123891MMM168557MMM
1 Solution

Accepted Solutions
Not applicable

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;

View solution in original post

4 Replies
Not applicable

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;

View solution in original post

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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!

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein