topic Re: unmapped columns in QlikView

unmapped columns

Fri, 18 Sep 2015 12:19:14 GMT

Hi ,

I have a scenario as below

map:

Mapping Load.

X ,Y

...

SQL select * from ....table1

Load

ApplyMap('X .......) .

SQL ...table 2

now my issue is table 1 other than X and Y columns I have 4-5 additional columns which is very much required.

As a 3rd step ..I created a load statement again to table 1 to have the columns

Load.

DATE,

DATE2.

FROM tABLE1.

Mapping load works very good. But my additional columns that is Z , date 1 ,date 2 if I include them in the report. 2 problems occurs

1. if I include in the expression I get no data for these 3 columns.

2. if I include these 3 columns in the dimension I get data duplicated too much..

Re: unmapped columns

sunny_talwar — Fri, 18 Sep 2015 12:21:00 GMT

I don't really understand your problem here. Would it be possible to share some sample data with your expected output?

Re: unmapped columns

Fri, 18 Sep 2015 12:27:05 GMT

You should be more clear in the request,

please include the code of your load script and if you can also have a set of sample data,

so that they can experience directly and provide the necessary information more precisely.

Regards

Re: unmapped columns

Fri, 18 Sep 2015 12:40:50 GMT

hi sunny,

The tables are bigger.. not sure how to give sample.. the scenario is very like this..

Iam using Table 1 as for my mapping load

table 2 -- for apply map...

now I have the common field from table 1 and table 2. for ex EmpId .. But I need the other columns of the table1 ..so I created again load statement

Load ....from Table 1 .

which consists the entire columns of table 1.

So now If I include those columns in my report

2 problems occurs

1. if I include in the expression I get no data for these 3 columns.

2. if I include these 3 columns in the dimension I get data duplicated too much..

Let me know its clear?

Re: unmapped columns

sunny_talwar — Fri, 18 Sep 2015 12:47:56 GMT

Unfortunately, I still don't completely understand what you are trying to achieve?

Re: unmapped columns

sudeepkm — Fri, 18 Sep 2015 12:52:37 GMT

My assumption is that you have

1. Table 1 that has all the columns you need for your report.

2. Table 2 that has a field EMP_ID that exists in Table 1 too.

3. A mapping load of Table 1 because you need a field from Table 1 in to Table 2 as per your mapping load logic.

If you have to load every column form Table 1 then associate it with Table 2 based on Emp_ID then why do you need the mapping load.

If table 1 is your master table then you may think of joining table 2 to table 1.

It would be great if you could provide sample data with column details and dependencies.

Re: unmapped columns

Fri, 18 Sep 2015 12:53:08 GMT

sunny my question is how can I load the original mapping table with full columns.

Ex: Lets think I am going to do Mapping Load ......from Table1..

Now how do I load the original table 1 .

Is mapping Load table just a look table?

Re: unmapped columns

sunny_talwar — Fri, 18 Sep 2015 12:55:53 GMT

There are couple of ways to do it. The easier one is to create multiple mapping loads with two fields each (one of them will always be EmpID)

Re: unmapped columns

Fri, 18 Sep 2015 13:01:13 GMT

But if the information you are complete in Table 1 applies directly maps to the first full charge of the table

If I understand you can use a script like:

MappingTable:

Load

X ,Y

SQL select * from ....table1

Table1:

Load.

ApplyMap(X,....) as mapping,

DATE,

DATE2.

From SQL select * from ....table1

Re: unmapped columns

Fri, 18 Sep 2015 13:33:15 GMT

hi sunny,

I made a sample here

map1:
MAPPING LOAD Distinct
prodid,         ,
customerId

    ;

SQL SELECT prodid,         ,
customerId
FROM ods.products
;

map2
LOAD
CustomerId   ,
ApplyMap ('map1',customerId ) as CustId
name,
    Address
    street
    bus
    country
    ;

SQL SELECT
*
FROM ods.customer

now on my report.. i have all the columns of map2.now i need columns of map 1 also..so i created another load statement

Products:
MAPPING LOAD
prodid,         ,
customerId ,
        Product serial no,
        description,
name
        status

    ;

SQL SELECT *
FROM ods.products
;

now when i try to use the above columns
prodid,         ,
customerId ,
        Product serial no,
        description,
name
        status in the report

1. if I include in the expression I get no data for these 3 columns.

2. if I include these 3 columns in the dimension I get data duplicated too much..

Re: unmapped columns

Fri, 18 Sep 2015 13:35:08 GMT

hi genarro.

But my date 1 and X and Y are in table 1 ..how could give as SQL from table 1 here

Table1:

Load.

ApplyMap(X,....) as mapping,

DATE,

DATE2.

From SQL select * from ....table1

this above load is from Table 2

Re: unmapped columns

Fri, 18 Sep 2015 13:47:13 GMT

sorry genarro,

Z, Date1 and Date 2 are from Table 1 and X and Y only in Table 2

Re: unmapped columns

Fri, 18 Sep 2015 14:22:45 GMT

Hi Sunny,

I hope it is little clear now .it was typo mistake

now on my report.. i have all the columns of map2.now i need columns of map 1 also..so i created another load statement

Products:
//MAPPING LOAD ( It is normal LOAD.. it is a typo mistake)
prodid,         ,
customerId ,
        Product serial no,
        description,
name
        status

    ;

SQL SELECT *
FROM ods.products

Re: unmapped columns

Fri, 18 Sep 2015 14:28:28 GMT

Okay, but what is the relationship between Table 1 and Table 2?
The X and Y are present only in Table1?
If you have a way to relate you can upload them to join Left partento from that complete and adding columns mapped into left.

I thought I had understood that the Table1 and Table2 were the same except that the first is a presnete applyMap, but I was wrong

Re: unmapped columns

Fri, 18 Sep 2015 14:34:13 GMT

Gennaro,

yes relationship between Table 1 and Table 2 is only X. and Y .. and X is the common id we can say...

now when I tried to load the table 1 to get the other columns like Z, Date1 and Date 2 using below

Load

Z, Date1 and Date 2

from ......Table 1;

#After this when I use Z, Date1 and Date 2 on the report as a dimension or an expression..

1. if I include in the expression I get no data for these 3 columns.

2. if I include these 3 columns in the dimension I get data duplicated too much

Re: unmapped columns

Fri, 18 Sep 2015 14:42:43 GMT

Can you post the portion of scritpt load for mapping table table1 and table2 ?

Re: unmapped columns

Fri, 18 Sep 2015 14:43:41 GMT

Sorry I see now the the code was just posted

Re: unmapped columns

Fri, 18 Sep 2015 14:50:04 GMT

Hi benny,

try whit a script like this:

map1:

MAPPING LOAD Distinct

prodid,

customerId

From

SQL SELECT prodid,

customerId FROM ods.products;

map2:

LOAD

CustomerId,

ApplyMap ('map1',customerId ) as CustId,

name,

Address,

street,

bus,

country

FROM

SQL SELECT

FROM ods.customer;

Products:

LOAD

prodid,

customerId,

[Product serial no],

description,

name,

status

FROM

SQL SELECT *

FROM ods.products;

Left Join(Products)

LOAD

CustomerId,

CustId,

name as nameCustomer, //given that field name exists in the product table here add an alias

Address,

street,

bus,

country

RESIDENT map2;

Drop table map1;

Drop table map2;

Regards

Gennaro

Re: unmapped columns

Fri, 18 Sep 2015 17:23:00 GMT

Hi gennaro

iam facing the same issue. if you put thise under expression its blank ..in dimension it is duplicating 3-4 times