Load the Excel file and convert the column in row

[iahmadmca]

Hi Team,

I have a one excel with some data. The excel data i want to arrange in  QlikView script.

Please find the below example.

I have attached sample excel data.

Based on excel data i need to store Excel data in QVD with below format.

Sl No	Dealer Code	Dealerships Name	Location	Facility Type	Zone	JDP Dealers	Month	Tot Cust. Contacted	Tot MRS Call Generated
1	GR042	A	Kolkata		East		Jan	1	1
2	GR042	B	Kolkata		East		Feb	2	2
3	GR042	C	Kolkata		East		Mar	3	3
4	GR042	A	Kolkata		East		Apr	4	4
5	GR042	B	Kolkata		East		May	5	5
6	GR042	C	Kolkata		East		Jun	6	6
7	GR042	A	Kolkata		East		Jul	7	7
8	GR042	B	Kolkata		East		Aug	8	8
9	GR042	C	Kolkata		East		Sep	9	9
10	GR042	A	Kolkata		East		Oct	10	10
11	GR042	B	Kolkata		East		Nov	11	11
12	GR042	C	Kolkata		East		Dec	12	12

I would like to request you to please help me to do this.

Regards,

Irshad Ansari

[gavinlaird · Accepted solution]

Hi Irshad,

The load script below will produce the format you're looking for:

//Step 1: Crosstable the data so you have one column for measures (Tot Cust Contacted, Tot MRS Call Generated), and one for values.
TempData:
Crosstable ('Measure','Value',7) 
LOAD * FROM
[C:\Users\z090814\Downloads\Test Data.xlsx]
(ooxml, embedded labels, header is 1 lines);

//Step2: Identify the month of each measure
TempData2:
LOAD [Sl No],[Dealer Code],[Dealerships Name],Location,[Facility Type],Zone,[JDP Dealers]
	,[Sl No]&[Dealer Code]&[Dealerships Name]&Location&[Facility Type]&Zone&[JDP Dealers]&Alt(Num(Right(Measure,2)),Num(Right(Measure,1)),0) as DedupeKey
	,if(WildMatch(Measure,'Tot Cust. Contacted*'),'Tot Cust. Contacted'
		,if(WildMatch(Measure,'Tot MRS Call Generated*'),'Tot MRS Call Generated')) as Measure
	,Month(MakeDate(2000,1+Alt(Num(Right(Measure,2)),Num(Right(Measure,1)),0))) as Month
	,Value
Resident TempData;

//Step3: Create a new table with only one row per unique combination of Sl No, Dealer Code, Dealerships Name, Location, Facility Type, Zone, JDP Dealers, and Month.
Data:
LOAD DISTINCT DedupeKey
	,[Sl No],[Dealer Code],[Dealerships Name],Location,[Facility Type],Zone,[JDP Dealers],Month
Resident TempData2;

//Step4: Pull in values , using a different columns for the two measures.
Left Join (Data)
LOAD DedupeKey
	,Value as [Tot Cust. Contacted]
Resident TempData2
	Where Measure = 'Tot Cust. Contacted';
	
Left Join (Data)
LOAD DedupeKey
	,Value as [Tot MRS Call Generated]
Resident TempData2
	Where Measure = 'Tot MRS Call Generated';
	
Drop Fields Measure, DedupeKey;
Drop Tables TempData, TempData2;

Store Data into [Location/ExportFileName.qvd](qvd);

QlikView concatenates a number to the end of each column with the same name, so the second "Tot Cust. Contacted" column is loaded as "Tot Cust. Contacted1", the third is loaded as "Tot Cust. Contacted2", etc. The "TempData2" load uses that number to determine the month.

[Taoufiq_Zarra]

there are several ways.
I propose the following method

Qlikview File is attached

Input1:
load
[Sl No],
[Dealer Code],
[Dealerships Name],
Location,
[Facility Type],
Zone,
[JDP Dealers],
Month(MakeDate(2000, 1, 1)) As Month,
[Tot Cust. Contacted],
[Tot MRS Call Generated]

 resident Data;
;



for i=1 to 11 step 1
Input1:

load
[Sl No],
[Dealer Code],
[Dealerships Name],
Location,
[Facility Type],
Zone,
[JDP Dealers],
Month(MakeDate(2000,$(i)+1, 1)) As Month,
[Tot Cust. Contacted$(i)] as [Tot Cust. Contacted],
[Tot MRS Call Generated$(i)]  as [Tot MRS Call Generated]
 resident Data;
;

next


drop table Data;

Input is your Data as :

Data:
LOAD [Sl No],
[Dealer Code],
[Dealerships Name],
Location,
[Facility Type],
Zone,
[JDP Dealers],
[Tot Cust. Contacted],
[Tot MRS Call Generated],
[Tot Cust. Contacted1],
[Tot MRS Call Generated1],
[Tot Cust. Contacted2],
[Tot MRS Call Generated2],
[Tot Cust. Contacted3],
[Tot MRS Call Generated3],
[Tot Cust. Contacted4],
[Tot MRS Call Generated4],
[Tot Cust. Contacted5],
[Tot MRS Call Generated5],
[Tot Cust. Contacted6],
[Tot MRS Call Generated6],
[Tot Cust. Contacted7],
[Tot MRS Call Generated7],
[Tot Cust. Contacted8],
[Tot MRS Call Generated8],
[Tot Cust. Contacted9],
[Tot MRS Call Generated9],
[Tot Cust. Contacted10],
[Tot MRS Call Generated10],
[Tot Cust. Contacted11],
[Tot MRS Call Generated11],
F32
FROM
[.\Test Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[iahmadmca]

Thanks for your valuable time.

Regards,

Irshad Ansari

[iahmadmca]

Hi gavinlaird,

Could you please share the QVW file

Regards,

Irshad Ansari

[Brett_Bleess]

Be sure to use the Accept as Solution button on any post(s) that help you with your solution.  Regarding the other poster attaching QVW, it is not necessary, simply copy the script and paste it into a new QVW file...  Using the solution button gives the poster credit for the help and lets the other Community Members know what worked...

You can also use the following area of Community to search for potential solutions as well:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett