topic Re: Data Load into QlikView in QlikView

Data Load into QlikView

rajender_qlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi Users,

I am looking to change the data which i have in excel to column-wise so I can load into QlikView.

I have data like this as below in the excel file.

Date	08/21/2017 7:52:09 AM
Machine	COMP-C301-A
COMPRESSOR Section
COMP-C301-A.COMP-C301-A.CSPD.COMPRESSOR SPEED FROM MARKER, MARKER SPEED	RPM	334
COMPRESSOR CYLINDER Section	Index	1	2	3	4
COMP-C301-A.1.1CTD.COMP CYL, DISCHARGE TEMPERATURE MEASURED - DEG F, DISCHARGE TEMPERATURE	DGF	260	247	274	258
COMP-C301-A.1.1CTS.COMP CYL, SUCTION TEMPERATURE MEASURED - DEG F, SUCTION TEMPERATURE	DGF	111	116	115	113
COMPRESSOR THROW Section	Index	1	2	3	4
COMP-C301-A.THROW 1.COMPRESSOR VIBRATION G 5.5KHZ - 200KHZ.1X	G	1.874541998	1.239926815	1.263736367	1.994505525
COMP-C301-A.THROW 1.VIBRATION G 1HZ - 8KHZ VT LOW CORNER.1X	G	6.923077106	2.832722902	4.468864441	3.064713001
COMP-C301-A.THROW 1.VIBRATION G 180HZ - 8KHZ VT LOW CORNER.1X	G	6.776556969	2.344322443	4.322344303	2.783882856

So I need QlikView script which changes the excel data into this format as below when i load it in QlikView.

Date	Machine	Section	Tag	Index	Unit	Data
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR Section	COMP-C301-A.COMP-C301-A.CSPD.COMPRESSOR SPEED FROM MARKER, MARKER SPEED	RPM	334
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTD.COMP CYL, DISCHARGE TEMPERATURE MEASURED - DEG F, DISCHARGE TEMPERATURE	1	DGF	260
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTD.COMP CYL, DISCHARGE TEMPERATURE MEASURED - DEG F, DISCHARGE TEMPERATURE	2	DGF	247
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTD.COMP CYL, DISCHARGE TEMPERATURE MEASURED - DEG F, DISCHARGE TEMPERATURE	3	DGF	274
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTD.COMP CYL, DISCHARGE TEMPERATURE MEASURED - DEG F, DISCHARGE TEMPERATURE	4	DGF	258
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTS.COMP CYL, SUCTION TEMPERATURE MEASURED - DEG F, SUCTION TEMPERATURE	1	DGF	111
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTS.COMP CYL, SUCTION TEMPERATURE MEASURED - DEG F, SUCTION TEMPERATURE	2	DGF	116
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTS.COMP CYL, SUCTION TEMPERATURE MEASURED - DEG F, SUCTION TEMPERATURE	3	DGF	115
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR CYLINDER Section	COMP-C301-A.1.1CTS.COMP CYL, SUCTION TEMPERATURE MEASURED - DEG F, SUCTION TEMPERATURE	4	DGF	113
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.COMPRESSOR VIBRATION G 5.5KHZ - 200KHZ.1X	1	G	1.874541998
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.COMPRESSOR VIBRATION G 5.5KHZ - 200KHZ.1X	2	G	1.239926815
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.COMPRESSOR VIBRATION G 5.5KHZ - 200KHZ.1X	3	G	1.263736367
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.COMPRESSOR VIBRATION G 5.5KHZ - 200KHZ.1X	4	G	1.994505525
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 1HZ - 8KHZ VT LOW CORNER.1X	1	G	6.923077106
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 1HZ - 8KHZ VT LOW CORNER.1X	2	G	2.832722902
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 1HZ - 8KHZ VT LOW CORNER.1X	3	G	4.468864441
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 1HZ - 8KHZ VT LOW CORNER.1X	4	G	3.064713001
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 180HZ - 8KHZ VT LOW CORNER.1X	1	G	6.776556969
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 180HZ - 8KHZ VT LOW CORNER.1X	2	G	2.344322443
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 180HZ - 8KHZ VT LOW CORNER.1X	3	G	4.322344303
08/21/2017 7:52:09 AM	COMP-C301-A	COMPRESSOR THROW Section	COMP-C301-A.THROW 1.VIBRATION G 180HZ - 8KHZ VT LOW CORNER.1X	4	G	2.783882856

Any help is appreciated.

Thanks.

Re: Data Load into QlikView

CarlosAMonroy — Mon, 18 Dec 2017 22:17:49 GMT

Hi Rajender,

When you load the excel file, QV launches a wizard, so you can achieve what you want. May be doing it a cross table or there are many other transformations that you can do using the wizard.

Hope that helps,

Carlos

Re: Data Load into QlikView

rajender_qlik — Mon, 18 Dec 2017 22:20:29 GMT

Hi Carlos,

Thanks for your reply. I have tried using those transformations but it doesn't work for what I was looking for.

Re: Data Load into QlikView

mariusz_kumansk — Mon, 18 Dec 2017 22:31:52 GMT

Could you send this excel file??

Re: Data Load into QlikView

rajender_qlik — Tue, 19 Dec 2017 01:54:12 GMT

Hi Mariusz,

i have attached sample data which has excel data in excel tab and also has qlikview format which i need to get it when i load excel into qlikview.

Re: Data Load into QlikView

mariusz_kumansk — Wed, 20 Dec 2017 00:18:04 GMT

My quick solution, maybe this helps you

machineInfo:

LOAD

Machine,

Date

FROM

(ooxml, embedded labels, table is Excel, filters(

Rotate(right)

));

LET vMachine = PEEK('Machine',0,'machineInfo');

LET vDate = PEEK('Date',0,'machineInfo');

Data:

CrossTable(X, Data, 3)

LOAD

RecNo() as %KeyRow,

A as Tag,

B as Unit,

FROM

(ooxml, no labels, table is Excel)

WHERE

(not WildMatch(A, '*Section*'));

Left Join

LOAD

RowNo() as %KeyRow ,

'$(vMachine)' as Machine,

'$(vDate)' as Date,

trim(@1) as Section

FROM

(ooxml, no labels, table is Excel, filters(

Replace(1, top, StrCnd(contain, 'Section', not, case))

));

DROP Table machineInfo;

DROP Fields %KeyRow, X;

Re: Data Load into QlikView

rajender_qlik — Wed, 20 Dec 2017 15:04:21 GMT

Thanks for the app and you are missing the Index column data. That's what I am unable to change the data into.