Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iahmadmca
Contributor
Contributor

Load the Excel file and convert the column in row

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 NoDealer CodeDealerships NameLocationFacility TypeZoneJDP DealersMonthTot Cust. ContactedTot MRS Call Generated
1GR042AKolkata East Jan11
2GR042BKolkata East Feb22
3GR042CKolkata East Mar33
4GR042AKolkata East Apr44
5GR042BKolkata East May55
6GR042CKolkata East Jun66
7GR042AKolkata East Jul77
8GR042BKolkata East Aug88
9GR042CKolkata East Sep99
10GR042AKolkata East Oct1010
11GR042BKolkata East Nov1111
12GR042CKolkata East Dec1212

 

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

Regards,

Irshad Ansari

1 Solution

Accepted Solutions
gavinlaird
Contributor III
Contributor III

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.

View solution in original post

5 Replies
gavinlaird
Contributor III
Contributor III

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.

View solution in original post

Taoufiq_Zarra

there are several ways.
I propose the following methodCapture.PNG

 

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);

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
iahmadmca
Contributor
Contributor
Author

Thanks for your valuable time.

Regards,

Irshad Ansari

iahmadmca
Contributor
Contributor
Author

Hi gavinlaird,

Could you please share the QVW file

Regards,

Irshad Ansari

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.