Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Complete empty fields with data from another table

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?

1 Solution

Accepted Solutions
gsbeaton
Contributor II

Re: Complete empty fields with data from another table

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 Replies
kawamuram
Contributor II

Re: Complete empty fields with data from another table

Hi,

Did you try apply map?

Apply Map Functions - Sources

Best Regards,

gsbeaton
Contributor II

Re: Complete empty fields with data from another table

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

Not applicable

Re: Complete empty fields with data from another table

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

Not applicable

Re: Complete empty fields with data from another table

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 )
Community Browser