update an empty field

[#1]

Again I have to post here as I am not able to solve it by myself [:$]

I'm trying to set up an sales analysis tool, at the moment i just have the table with all the sales´

Each row has a invoice number and a material number and in the most cases also the product group.

But not very rare, the product group is missing, so I set up an excel file where i linked the Material to the product groups

-> so that Qlikview can get the right PRODUCT GROUP via the MATERIAL

Unfortunately its not possible for me to make it that way. I have read some information about join and concatenate but I think i have to solve it another way.

Thank you in advance for every proposal to update my empty PRODUCT GROUP fields!

Christoph

[#2]

Hello!

try like:

---------------------------------------------------------------------------------------------

TAB1:

LOAD [INVOICE NO]

,[MATRERIAL NO]

,MATERIAL

FROM......................

RIGHT JOIN LOAD MATERIAL,[PRODUCT NO]

FROM.................

TAB2:

LOAD [INVOICE NO]

,[MATRERIAL NO]

,MATERIAL

,IF(ISNULL([PRODUCT GROUP]), (VALUE THAT U WANT TO ENTER),[PRODUCT GROUP] AS PRODUCT GROUP

RESIDENT TAB1;

DROP TABLE TAB1;

-----------------------------------------------------------------------------------------

hope this ll help you......

[#3]

Thank you a lot for your proposal djpdon123,

I have tried your idea but when i load my sales and make a RIGHT JOIN i'm completely running out of virtual and logical memory 😞

i will explain it in detail:

The first load-statement of my sales is the following:

Sales:

load

Invoice No,

Material,

Product Group

from ....

I have to say that i need the Product Group here as my assignment (Material to Product Group) is not that perfect as the assignment of the system (where the figures come from)

So I do take the Product Group from the Sales Report and just want to fill that rows which are empty!

For my understanding JOIN is not the right statement (but i'm more or the less a QV-noob)

As you can see the sales load above, additionally i have set up an excel-sheet for the assignment which looks like the following:

ProductGroup_Assignment:

load

Material,

Product Group

from ....

the if-statement you (djpdon123) have suggested seems more fitting, but concerning this issue I am not sure about the (VALUE THAT U WANT TO ENTER) statement .. dont know how it should look like ...

Hope there is anybody out there able and willing to help me 😕

thank you in advance,

christoph

[#4]

if u can upload a sample QVW or sample data file then that will be easy to solve..............

[#5]

You mean just the QVW file without the QVD and all the data?

Hm the following example may also help:

SALES:
Invoice No	Material	Product Group	Amount	Invoice Date
101	E-532	Chairs	500	01.01.2010
101	X-300	Desks	700	30.03.2010
105	F-394		1000	01.04.2010
107	KK-22	Doors	800	02.05.2010

ProductGroup_Assignment:
Material	ProductGroup
E-532	Chairs
X-300	Desks
F-394	Doors
KK-22	Doors

[tanelry · Accepted solution]

Use mapping load and applymap function instead of join. This is faster and cleaner.

ProductGroupMap:
mapping load distinct
Material, ProductGroup
from xyz.xls;

SALES:
load
InvoiceNo,
Material,
if(len(ProductGroup)>0,ProductGroup,applymap('ProductGroupMap', Material)) as ProductGroup,
Amount,
...

[#7]

Check the file

[#8]

Thanks a lot for your reply Tanel Rüütli

I think the solution via mapping load fits perfectly and from my point of view it should also work 🙂

Unfortunately I cannot test it today, but I'm very hopefully.

@djpdon123: thank you a lot for your effort, too.

I understand your idea but I think there is a problem with the following line, as there is not just 'Doors' missing (could also be Desk or Chair etc..)

if

([Product Group]='','Door',[Product Group]) as [Product Group]

I hope you can also agree with Tanels solution.

Thank you so much, I would not have known how to solve this ...

the helpfull QV-community is impressive 🙂

[#9]

Tanel,

today I had time to test your proposal which works fine in theory.

In practice test I get the Material code as ProductGroup. I have used exactly your script but the system is loading the Material field as ProductGroup instead of the ProductGroup (from mappingtable) as ProductGroup WHEN len=0.

I dont know what do, i dont know where the mistake is 😞

[#10]

I got it by now.

I had to load the Sales script sheet first and then the mapping one, which does not make a good sense to me but okay ...

Thank you for the hints 🙂