topic Re: Doubt in QlikView

Doubt

NavinReddy — Tue, 04 Aug 2015 13:12:44 GMT

Dear Experts,

I have 10-20 Excel sheets data for JUN month again

i have 10-20 Excel sheets data for JULY month, it will get add for all months

here i just want to add all the months data and i want to show month trend

im struck here don't know what technique should i follow

can some one please suggest any idea or your valuable suggestions please

Many Thanks

Niranjan

Re: Doubt

NavinReddy — Tue, 04 Aug 2015 16:41:32 GMT

I need you guys suggestions

Re: Doubt

maximiliano_vel — Tue, 04 Aug 2015 18:52:54 GMT

Could you please send an example of your data?

Re: Doubt

Anonymous — Tue, 04 Aug 2015 19:02:49 GMT

Modify and Try below script:

FOR EACH file in FileList('C:\Qlik\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET sheetName = peek('TABLE_NAME', i, 'Temp');

Table:

Load * ,

Month(Month) as Month,

MonthName(Month) as MonthName,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)], filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));

NEXT i

Drop table Temp;

Final:

Concatenate

LOAD *

Resident Table;

DROP Table Table;

Re: Doubt

NavinReddy — Wed, 05 Aug 2015 08:12:56 GMT

Hey Max,

but its like common fields for every month,June month i will get same fields with June data

July month i will get same fields with July data

(what im doing here every month im copy pasting data into one master file with the same fields)

is there any better approach we will handle this problem

Best Regards,

Niranjan

Re: Doubt

tamilarasu — Wed, 05 Aug 2015 08:33:10 GMT

Another Idea,

Paste the excel files in a folder and create a qvw file with the below script. It will concatenate all the fields.

Consolidation:
LOAD *,
FileName() as FileName,
FilePath() as FilePath
FROM
[*.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let me know, If you need example file.

Re: Doubt

NavinReddy — Wed, 05 Aug 2015 14:34:54 GMT

Hi Nagaraj,

That would be great if can i get QVW

Best Regards,

Niranjan

Re: Doubt

maximiliano_vel — Wed, 05 Aug 2015 14:43:11 GMT

The approach of neetha is accurate, also if you have some sample you can upload it wold be easier to assist you.

Regards

Re: Doubt

NavinReddy — Wed, 05 Aug 2015 17:09:10 GMT

Hi Neetha,

Im little bit confused using this code can you please provide sample QVW

if can i get from you that would be great help for me

first i will try with sample files then i will implement in my main file

Best Regards,

Niranjan

Re: Doubt

tamilarasu — Thu, 06 Aug 2015 02:00:36 GMT

Hi Niranjan K,

Check the attachment and let me know whether this is fine for you. If not, please post some sample data.