topic Re: Join Data from Excel in QlikView

Join Data from Excel

Thu, 31 Jul 2014 18:33:21 GMT

Hello everybody

I have a problem with a load script

I have a fact table with all the information and I need to add a colunm from a Excel File. The problem is that some of the data in the table not apear in the excel File. Example

Fact table (prior Join)

ITEM

00001

00002

00003

00004

00005

Excel

ITEM ITEM_KEY

00001 101001

00004 101004

00005 101005

Fact table (With a simple Join)

ITEM ITEM_KEY

00001 101001

00002 -

00003 -

00004 101004

00005 101005

What I need is, if the value item is in the exel (relation table) the field in ITEM_KEY needs to be equal to the value in the excel, but if the value is not in the Excel the Load need to pass the value of the ITEM column. The result I need for the load is the next:

ITEM ITEM_KEY

00001 101001 (This value is from the Excel)

00002 00002 (This value is from the Item column form the same fact table)

00003 00003 (This value is from the Item column form the same fact table)

00004 101004 (This value is from the Excel)

00005 101005 (This value is from the Excel)

Can anyone could help me

Re: Join Data from Excel

joshabbott — Thu, 31 Jul 2014 18:45:11 GMT

In the excel spreadsheet load, do a preceding load on top of it. For example, your load may look something like:

Load

ITEM

,ITEM_KEY

from excel;

Try this:

Load

ITEM

,Alt(ITEM_KEY, ITEM) as ITEM_KEY

Load

ITEM

,ITEM_KEY

from excel;

The alt should get the first non-null value and return it to the field.

Re: Join Data from Excel

joshabbott — Thu, 31 Jul 2014 18:47:04 GMT

Better yet, you may not need to do the preceding load at all, instead of LOAD ITEM_KEY

Try:

LOAD

Alt(ITEM_KEY,ITEM) as ITEM_KEY

Re: Join Data from Excel

maxgro — Thu, 31 Jul 2014 19:38:27 GMT

you can use a mapping load

Excel:

mapping load * inline [

ITEM , ITEM_KEY

00001 , 101001

00004 , 101004

00005 , 101005

];

[Fact table]:

load

ITEM,

ApplyMap('Excel', ITEM, 'Value not found ' & ITEM) as ITEM_KEY;

load * inline [

ITEM

00001

00002

00003

00004

00005

];