topic Re: Concatenation in QlikView

Concatenation

Mon, 14 Oct 2013 05:19:47 GMT

Hi All,

I have Excel files Demand, Demand14.

Demand Contains Project Number, Project Name and Demand14 contains Project Number, Project Name in one sheet(Demand14) and Project Number in another sheet(PNum).

My requirement is i want to get the project details which are mentioned in PNum sheet from Demand14.

I want to add them to Demand data

Totally My Req is as follows

Project Number	Project Name
1	A
2	B
3	C
4	D
5	E
11	K
12	L
13	M
14	N
15	O

I'm attaching my EXCel files and QVW, Please have a look and give me some suggestion.

Thanks,

Chiru

Re: Concatenation

Mon, 14 Oct 2013 05:30:49 GMT

rename it by using 'as' and then try to use

Re: Concatenation

Mon, 14 Oct 2013 05:36:47 GMT

Check attached file or script below.

Change PATH to your directory.

SET PATH = 'C:\Users\IA-User\Desktop\'; ////CHANGE DIRECTORY HERE

DEMAND:

LOAD [Project Number],

[Project Name]

FROM

$(PATH)Demand14.xlsx

(ooxml, embedded labels, table is Demand14);

Right Join(DEMAND)

LOAD [Project Number]

FROM

$(PATH)Demand14.xlsx

(ooxml, embedded labels, table is PNum);

join(DEMAND) ////Concatinate here is okay too

LOAD [Project Number],

[Project Name]

FROM

$(PATH)Demand.xlsx

(ooxml, embedded labels, table is Demand);

Re: Concatenation

Mon, 14 Oct 2013 05:48:56 GMT

Hi,

Swap the table loading order. Load Demand14 with join first and then load the Demand table with concatenation.

Re: Concatenation

CELAMBARASAN — Mon, 14 Oct 2013 05:53:12 GMT

You could use

Load

ProjectNumber

From PNum sheet.

Left Join

Load

ProjectNumber, //Field should be same as the above script ProjectNumber

ProjectName

From Demand14 Sheet;

Concatenate result to the table which is loaded from Demand.

Or something like

Demand:

LOAD *

FROM

[$(FilePath)\$(Demand14ReportName)]

(ooxml, embedded labels, table is [$(Demand14ReportSheetName)]);

Inner Join

LOAD

[Project Number]

FROM

[$(FilePath)\$(Demand14ReportName)]

(ooxml, embedded labels, table is [$(Demand14ProjReportSheetName)]);

Concatenate(Demand)

LOAD *

FROM

[$(FilePath)\$(DemandReportName)]

(ooxml, embedded labels, table is [$(DemandReportSheetName)]);

Re: Concatenation

Mon, 14 Oct 2013 06:30:57 GMT

Hi Adhimulam,

Thanks for Your reply, This is exactly what i needed.

Thanks to all who gave suggestion to me.

Thanks,

Vamsi.

Re: Concatenation

Mon, 14 Oct 2013 06:56:48 GMT

When you load the files first, load Demand.xlsx file

LOAD [Project Number],
[Project Name]
FROM
Demand.xlsx
(ooxml, embedded labels, table is Sheet1);

When you load second file Demand14.xlsx file, In file wizard: Type box change the default sheet to Pnum sheet in Tables dropdown box and click finish

Directory;
LOAD [Project Number]
FROM
Demand14.xlsx
(ooxml, embedded labels, table is PNum);

This is what I understood when you read your query.

If I am wrong, please expalin the query.

Thanks

prasadhet