How to "vlookup" in other tables - Qlik Community

icaro_povoa · ‎2016-02-12

Dear All,

I am new on Qlik and I want to build a script that combine 2 tables.

The first table is

Field NB	Responsible
1	Marcus
2	Steven
3	Joe
4	Elizabeth

and the second one is

Field NB	Acreage	Location	Season
1
2
3
4

Considering that the field numbers are unique and cannot repeat, how can I "search" (vlookup) the Responsible from the field and add it on my script below?

Load

FieldName

Acreage

Location

Season

From ......

Thank you for you help.

Regards

sunny_talwar · ‎2016-02-12

I would suggest just doing a regular join here:

Table:

LOAD [Field NB],

Responsible

FROM Source1;

Join(Table)

LOAD [Field NB],

Acreage,

Location,

Season

FROM Source2;

Anonymous · ‎2016-02-12

How is the value in the column 'field' getting generated? If it comes from a DB or a file (and not auto generated in QlikView) and if your first table only has 2 columns, you can think of using Mapping load and ApplyMap() to basically glue the column 'Responsible' with table second.

Something like:

table1:

Mapping load *

INLINE [

FIELD NB,Responsible

1, Marcus

2, Steven

3, Joe

4, Lizzy

];

table2:

Load *,

ApplyMap('table1',FIELD NB, 'N/A') as Responsible

inline [

FIELD NB, Acreage, Location, Season

1,,,

2,,,

3,,,

4,,,

];

icaro_povoa · ‎2016-02-15

Nakul1001, I don not have only two columns on my first table. Actually I have 8 tables, with at least five columns and with 10 or 15 different "Responsible Names" in each.

Any Other Option?

Sunny I could not make it work by your proposal. Can you provide more details?

Is it possible to create this "join" if the columns names of [NB Field] is are not the same in the to tables?
As written above I have 8 different tables for supervisors, how can I add all of them in the script of "Join Table".
If I have the [Nb Field] in one table but not in the other... will this work as a filter just showing the information that is displayed in both tables?

Thanks

icaro_povoa · ‎2016-02-15

Sunny T,

Only now I could use your suggestion and added multiples Tables and then multiple Join.

But unfortunately, now, everytime that I select a Responsible name, it is working as a filter.

In other words, if I have 1000 Field Nb on the place I am "join", and 10 on the "table", I´ll only see the 10 Field Nb where I have a Responsible. The other 990 will be removed from my graphic. Is it possible to keep the 990 Fields in the graphic?

Thank you

sunny_talwar · ‎2016-02-15

Is it possible to share a sample of data? It would be much easier to recommend something based on seeing what you have

Anonymous · ‎2016-02-15

In the 8 tables you have with around 5 columns, I believe FieldNB is the common field and this is what you want to use as a lookup column to connect all the 8 tables? Are there any other common columns? (eg: 'Responsible')

I am sure you must have tried using a prefix for join (eg: outer, left) to get all possible records in the result set ( in case you want to refer qv help: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptPrefixes/Outer.h...

(It may be a possibility that you have do cascading joins (joining first 2 set of tables and using that derived table to join with the 3rd and so on...)

Do send a screen of the tables with columns to understand the relationship better here.

icaro_povoa · ‎2016-02-16

Dear both,

Trying to explain it better.

I have the 7 tables that are my official control of the information ( 1 per crop and country). In parallel I am trying to implement a software that will substitute the excel files, but for doing that I need to validate, witch of the FieldNb are in the system and witch aren´t.

The filters I need to have are basically, Crop, Country, Region, and Responsible for the field, because I need to provide a detailed information for the managers who will push individually the Responsibles for imputing the information on the software.

The problem is the the responsible names( Supervisor) , are only displayed on the 7 Files that are in Excel, but are not in the software. What I need is to "look" the FieldNb that are in the Excel, and check if they are in the software. My initial idea was to ' Vlookup" on the Excel, the responsable names for the FieldNb already created on the software, so when I filter the Responsible name, I will get the Total NB of Fields in my Oficial source (Excel) x Nb of Fields in the software.

Using the Join, I could only " copy " the information from the Excel into the Software scrip, but this " removed" the fields that are not in both scripts.

Unfortunately I cannot send the database, but this is the script...

LOAD

'Brazil'as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 1

LOAD

'Argentina' as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 2

Load

'Brazil'as Country,

[Production Season] as ProductionCycle,

Field as PlotName2,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 3

LOAD

Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 4

LOAD

'Argentina' as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Sunflower' as Crop,

Status

FROM Source 5

LOAD

'Argentina' as Country,

[Production Season] as ProductionCycle,

Field as PlotName2,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 6

LOAD

'Argentina' as Country,

Field as PlotName2,

[Field Manager],

Supervisor,

Season as ProductionCycle,

'Sunflower' as Crop,

Status

FROM Source 7

LOAD

PlotName,

FieldName,

ProductionCycle,

Crop,

Country,

Grower,

ProductName,

SurfaceOfficial,

Hectare,

ProductionInstruction,

Operation_Phase,

Start_Date,

End_Date,

Status,

Operation_Name,

Target_Parent,

Commentaires,

Week,

FROM Software.

icaro_povoa · ‎2016-02-16

Dear All,

This is what I need.

In the graphic below, I have all my filters except the Responsible Name ( Supervisor). Notice that I have in blue the column of NbFields on my Software, in Red the Source 1 and in Green the Source 3.

Now, when I add the Responsible name on the filter, I only see the information from where the Responsible is. In this case the Source 1.

But I am sure that Ronald have Fields that were created in the Software as well.So in this case the blue column should be =<7, once 7 is the real information and by that is also the maximum. But as the software script does not have a column with the Responsible Name, when I applied the filter, Qlik understand that the column is in blanc.

Can you help me to develop this script?

Thanks

icaro_povoa · ‎2016-02-19

Dear Sunny

This is what I need.

In the graphic below, I have all my filters except the Responsible Name ( Supervisor). Notice that I have in blue the column of NbFields on my Software, in Red the Source 1 and in Green the Source 3.

Now, when I add the Responsible name on the filter, I only see the information from where the Responsible is. In this case the Source 1.

But I am sure that Ronald have Fields that were created in the Software as well.So in this case the blue column should be =<7, once 7 is the real information and by that is also the maximum. But as the software script does not have a column with the Responsible Name, when I applied the filter, Qlik understand that the column is in blanc.

The problem is the the responsible names( Supervisor) , are only displayed on the 7 Files that are in Excel, but are not in the software. What I need is to "look" the FieldNb that are in the Excel, and check if they are in the software. My initial idea was to ' Vlookup" on the Excel, the responsable names for the FieldNb already created on the software, so when I filter the Responsible name, I will get the Total NB of Fields in my Oficial source (Excel) x Nb of Fields in the software.

Using the Join, I could only " copy " the information from the Excel into the Software scrip, but this " removed" the fields that are not in both scripts.

Unfortunately I cannot send the database, but this is the script...

LOAD

'Brazil'as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 1

LOAD

'Argentina' as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 2

Load

'Brazil'as Country,

[Production Season] as ProductionCycle,

Field as PlotName2,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 3

LOAD

Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 4

LOAD

'Argentina' as Country,

Season as ProductionCycle,

Field as PlotName1,

[Field Manager],

Supervisor,

'Sunflower' as Crop,

Status

FROM Source 5

LOAD

'Argentina' as Country,

[Production Season] as ProductionCycle,

Field as PlotName2,

[Field Manager],

Supervisor,

'Corn' as Crop,

Status

FROM Source 6

LOAD

'Argentina' as Country,

Field as PlotName2,

[Field Manager],

Supervisor,

Season as ProductionCycle,

'Sunflower' as Crop,

Status

FROM Source 7

LOAD

PlotName,

FieldName,

ProductionCycle,

Crop,

Country,

Grower,

ProductName,

SurfaceOfficial,

Hectare,

ProductionInstruction,

Operation_Phase,

Start_Date,

End_Date,

Status,

Operation_Name,

Target_Parent,

Commentaires,

Week,

FROM Software.

Can you Help me with this ?

Thank you