topic Complete empty fields with data from another table in QlikView

Complete empty fields with data from another table

Tue, 24 Nov 2015 14:58:13 GMT

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?

Re: Complete empty fields with data from another table

Anonymous — Tue, 24 Nov 2015 16:27:35 GMT

Hi,

Did you try apply map?

Apply Map Functions - Sources

Best Regards,

Re: Complete empty fields with data from another table

gsbeaton — Tue, 24 Nov 2015 17:07:56 GMT

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

Re: Complete empty fields with data from another table

Thu, 26 Nov 2015 16:29:00 GMT

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

Re: Complete empty fields with data from another table

Thu, 26 Nov 2015 16:41:04 GMT

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 )