topic Re: Loading data from Excel in QlikView

Loading data from Excel

Fri, 08 Aug 2014 04:36:36 GMT

Hello All,

I am loading data from two Excel files A and B .

Excel File A has two field Job, Start Date and Start time. Excel File B has FriceID, Job Name, Start Date and Frequency.

The Problem is that data of Start date field of file A is not matches with data of Start date field of file B. And Data of Job of file A is not exactly similar with data of Job Name of file B, only few records between Job and Job Name are same. So how to connect the two table in Qlikview?

Thanks & Regards

Re: Loading data from Excel

sujeetsingh — Fri, 08 Aug 2014 04:43:29 GMT

Meenakshi,

It is rather related to data management and entity relationship logic than the Qlikview.

In data model it is just not to join or connect any two tables.

--You need to analyse the key as per your need

--You need to check one to many relationship between

tables.

--It is you who need to decide how to connect the tables.

If you want to connect on Job and Job Name just name both as Job they will be assocciated as per Qlikview concepts .

Re: Loading data from Excel

Fri, 08 Aug 2014 04:46:52 GMT

I agree with Sujeet.

You need to manage the data and entity relationship before coming to QlikView.

Thanks!

Re: Loading data from Excel

sudeepkm — Fri, 08 Aug 2014 04:57:57 GMT

based on your requirement you will have to identify the fields based on which you will need to associate two or more table.

Can you explain your requirement a little bit more?

for example if your clients have asked to see all the records from B which has matching records in A based on Job/ Job name then you may have to go for a join. it would be helpful if you can elaborate your requirement.

Re: Loading data from Excel

Fri, 08 Aug 2014 04:59:25 GMT

But the problem is that only few records are same in job field and job Name field. Can we connect them through Job and Job Name?

Re: Loading data from Excel

VishalWaghole — Fri, 08 Aug 2014 05:06:20 GMT

Hi Meenakhi,

If there are not more records the you can manually transform that Data.

or else try to use Mapping Load and Apply Map is possible with your scenario.

If you share some dummy data then it will give more clarity for us.

-- Regards,

Vishal Waghole

Re: Loading data from Excel

Fri, 08 Aug 2014 05:19:19 GMT

Hi Vishal,

I am not able to provide you dummy data. You are telling to use mapping Load and apply load.

Can you explain me what is mapping Load and apply load concept?

Re: Loading data from Excel

deepakqlikview_123 — Fri, 08 Aug 2014 05:31:21 GMT

plz chk below link

Mapping Load - Apply Map Question

Re: Loading data from Excel

VishalWaghole — Fri, 08 Aug 2014 05:32:23 GMT

Hi,

Please find below example for mapping load.

I have data like U.S.A., U.S., United State in country column but i want to display it through out US.

Data:

LOAD * Inline [

Id, Name, Country

1, John Black, U.S.A.

2, Steve Johnson, U.S.

3, Mary White, United State

4, Susan McDaniels, U

5, Dean Smith, US

6, HH, Ind

];

CountryMap:

Mapping LOAD * Inline [

Country, NewCountry

U.S.A., US

U.S., US

United State, US

U, US

];

NoConcatenate

FinalData:

LOAD Id,

Name,

ApplyMap('CountryMap',Country) as Country

Resident Data;

DROP Table Data;

Hope you understand concept.

-- Regards,

Vishal Waghole

Re: Loading data from Excel

Fri, 08 Aug 2014 06:15:23 GMT

You should load both tables separately and the use JOIN or KEEP to analyze the data.

By default as per the fields you mentioned, the 2 tables will get joined on Start Date field.

First understand what are your requirements and then analyze data and explore it.

Thanks,

Singh

Re: Loading data from Excel

amit_saini — Fri, 08 Aug 2014 06:21:00 GMT

Meenakshi,

Angad is right , Join concept will work in your case. For more details go through the attachment.

Thanks,
AS