LOAD DATA FROM EXCEL

[sona_sa]

Team,

I need to pull the data from attached xls. I tried by using crosstable method. Not able to do the same.

SHEET NAME - Sheet1

Please help me on the same.

Thanks

[Sammy_AK]

i have tried sourcing the data using the below script. keeping date and shift as columns and transforming the rest of the columns in rows. This transforms into 2 columns, i.e. ATTRIB which contains the columns names and VAL which contains its corresponding values.

Please note to define the connection string to connect to excel sheet as "CS" to use the below script as it is. else you need to update the connection string to adopt it to your connection string setup.  

QS Script:

Data:

CrossTable('ATTRIB', 'VAL', 2)
LOAD
"Date",
Shift,
"Gear Box Housing",
"Gear Box Housing1",
"Diff. Housing",
"Diff. Housing1",
"Cyld. Block",
"Cyld. Block1",
"Hall 2 (GBH+DH)",
"Hall 2 (GBH+DH)1",
"Hall 2 (GBH+DH)2",
Grinding,
Grinding1,
"HT Shop",
"HT Shop1",
"Gear & Shaft",
"Gear & Shaft1",
Lighting,
"AC Plant",
"Office Lighting",
"AC Plant1",
"Canteen and Other Area",
"Canteen AC Plant",
"Air Compressor",
"Air washers",
"Hall-1
Lights",
"Hall-2
Lights",
"Street Light",
STP,
"Service
Panel",
Tubewells
FROM [lib://CS/excel.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Not IsNull("Date") ;

exit Script;

[sona_sa]

Hi Sammy,

Lots of thanks for the same. But in this script I am missing one field, Field name should be any thing, Value is Unit, Prod. Like below pics.

Is it possible ?

[Sammy_AK]

Yes, below is the script:

Using QlikSense Association / Join we can achieve this

QS Association:

Data:
CrossTable('ATTRIB', 'VAL', 2)
LOAD
"Date",
Shift,
"Gear Box Housing",
"Gear Box Housing1",
"Diff. Housing",
"Diff. Housing1",
"Cyld. Block",
"Cyld. Block1",
"Hall 2 (GBH+DH)",
"Hall 2 (GBH+DH)1",
"Hall 2 (GBH+DH)2",
Grinding,
Grinding1,
"HT Shop",
"HT Shop1",
"Gear & Shaft",
"Gear & Shaft1",
Lighting,
"AC Plant",
"Office Lighting",
"AC Plant1",
"Canteen and Other Area",
"Canteen AC Plant",
"Air Compressor",
"Air washers",
"Hall-1
Lights",
"Hall-2
Lights",
"Street Light",
STP,
"Service
Panel",
Tubewells
FROM [lib://CS/excel.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Not IsNull("Date") ;

Units:
CrossTable('ATTRIB', 'UNIT', 1)
LOAD
'UOM' as tbl,
"Gear Box Housing",
"Gear Box Housing1",
"Diff. Housing",
"Diff. Housing1",
"Cyld. Block",
"Cyld. Block1",
"Hall 2 (GBH+DH)",
"Hall 2 (GBH+DH)1",
"Hall 2 (GBH+DH)2",
Grinding,
Grinding1,
"HT Shop",
"HT Shop1",
"Gear & Shaft",
"Gear & Shaft1",
Lighting,
"AC Plant",
"Office Lighting",
"AC Plant1",
"Canteen and Other Area",
"Canteen AC Plant",
"Air Compressor",
"Air washers",
"Hall-1
Lights",
"Hall-2
Lights",
"Street Light",
STP,
"Service
Panel",
Tubewells
FROM [lib://CS/excel.xlsx]
(ooxml, embedded labels, table is Sheet1)
where IsNull("Date") ;

Using Join:

the above code is required and additionally you need to append the below code:

Join (Data)

Load ATTRIB,UNIT
Resident Units;

Drop Table Units;