Complete empty fields with data from another table

[#1]

Hello everybody, I'm newby in Qlik and I'm facing this...

I have a text file from a barcode scanner, it gives me one row for each item lecture, but for some items it gives me the EAN (some type of item number) and for some other it gives me Internal item ID's.

Something like:

EAN1

ItemID1

EAN2

ItemID2

ItemID3

ItemID4

...

I'm able to distinguish both type of lines thats no problem

I have to cross it with another table in wich I have Internal ID's form all the articles.

I want to cross the text file with my database where I have a table with all EANs and Internal ID's to get one single table with the Internal article ID's from the scanner lecture.

ItemID1

ItemID2

.....

Could you help me with the script?

[Anonymous]

Hi,

Did you try apply map?

Apply Map Functions - Sources

Best Regards,

[gsbeaton · Accepted solution]

Hi Cesar,

The ApplyMap() function will help you here.

First you need to create a mapping table

MapEANtoInternalID
Mapping Load EAN, InternalID From yourtextfile.csv
--In your Load statement
//Load *,
IF(IDColumn=EAN,ApplyMap(MapEANtoInternalID,IDColumn,Null()),IDColumn)
From yoursource.QVD;

That will ensure that where you identify your EAN code, you will map the result to your text file, otherwise, you just use the ID that's already in the source data.

Hope that helps,

Kind regards

George

[#4]

Thanks a lot Mauri, there's a lot to study!

[#5]

Thank you very much George, the result is perfect, the function is really powerfull. I finally have ended, making a left join, with just one table with the exact data I needed, instead of three.

I'm going to put two notes bellow that made some difficult to me:

I did:

MapEANtoInternalID:

Mapping LOAD

EAN,

InternalID;

OLEDB connect [instructions...]

SQL SELECT

EAN,

InternalID

From tableofmydb;

• I wasn't sure where to put the connection... there worked fine.
• It's critical to put the column you are looking for in the first place (obvius )