topic Re: uploading in QlikView

uploading

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

hi all,

I have attached an excel data ..could you let me know how can i upload this document in qlikview so data is represented correctly . i tried using cross tab option but not giving me correct out put..

kindly suggest

Re: uploading

raadwiptec — Thu, 30 Jun 2016 10:55:52 GMT

hi any suggestions here?

Re: uploading

qlikview979 — Thu, 30 Jun 2016 12:14:17 GMT

Hi,

Please share your Expected output.

I hope this is help full to you

CrossTable(Estimate1, Data, 2)

LOAD [Printer Material Config],

Estimate,

Definite,

Estimate1,

Definite1,

Estimate2,

Definite2,

Estimate3,

Definite3,

Estimate4,

Definite4,

Estimate5,

Definite5,

Estimate6,

Definite6,

Estimate7,

Definite7,

Estimate8,

Definite8,

Estimate9,

Definite9,

Estimate10,

Definite10,

Estimate11,

Definite11

Re: uploading

remi_roland — Thu, 30 Jun 2016 12:34:32 GMT

Hi,

The transformation you try to perform is a really complex, I recommand to modify format of your excel file.

However if you really want to keep this format you can use following code, it should still work if you add month or printers in your excel file.

TABLETMP1:
LOAD rowno() as colindex, * FROM (ooxml, no labels, table is Sheet1, filters(Transpose()));

TABLETMP2:
NoConcatenate LOAD
colindex,
if(len(trim(@1))=0, peek(@1), @1) as @1,
if(len(trim(@2))=0, peek(@2), @2) as @2,
if(len(trim(@3))=0, peek(@3), @3) as @3
Resident TABLETMP1;

DROP Table TABLETMP1;

RENAME FIELD @1 TO TMP1;
RENAME FIELD @2 TO TMP2;
RENAME FIELD @3 TO TMP3;

LEFT JOIN LOAD rowno() as colindex, * FROM (ooxml, no labels, table is Sheet1, filters(Transpose()));

DROP FIELD @1;
DROP FIELD @2;
DROP FIELD @3;

LET nbfield = NoOfFields('TABLETMP2');

FOR i=0 to nbfield
LET CurrField = FieldName(i,TABLETMP2);
IF index(CurrField,'@') THEN
LET PrinterName= '['&FieldValue(CurrField,1)&'|'&i&']'; //Use index "i" to avoid error when the same printer is available multiple times.
RENAME FIELD $(CurrField) TO $(PrinterName);
ENDIF;
NEXT

TABLETMP3:
NoConcatenate LOAD * Resident TABLETMP2 where colindex<>1;
DROP TABLE TABLETMP2;

TABLETMP4:
CrossTable(PrintersTMP, Data,5) LOAD * Resident TABLETMP3;
DROP TABLE TABLETMP3;

FINAL:
NoConcatenate LOAD left(PrintersTMP,index(PrintersTMP,'|')-1) as Printers, * Resident TABLETMP4 where not isnull(Data);

RENAME FIELD TMP1 TO Quarter;
RENAME FIELD TMP2 TO Month;
RENAME FIELD TMP3 TO Type;
RENAME FIELD [Printer Material Config|5] TO [Estimate/Definite];
DROP FIELD colindex;
DROP FIELD PrintersTMP;
DROP TABLE TABLETMP4;

Hope it will help you.

Regards.

Remi.

Re: uploading

raadwiptec — Thu, 30 Jun 2016 13:58:59 GMT

there are multiple headers

Re: uploading

Peter_Cammaert — Thu, 30 Jun 2016 14:43:42 GMT

Searching the community reveals this ready-to-use solution: multi_header_pivot_import.qvw

Re: uploading

raadwiptec — Fri, 01 Jul 2016 14:46:50 GMT

hi remi,

can u send you the file. i try adapting. looks confused... can i have ur email?

Re: uploading

remi_roland — Fri, 01 Jul 2016 18:22:48 GMT

Hi Raadwiptec,

Please find enclosed the qvw file based on your excel file.

I added some comments to explain what I did.

You can use it as it is from the excel file you posted.

I keep on warning you on the input format, if you can

Please let me know if it work as you want and mark the discussion as resolved to make this script collaborative

Rémi.

Re: uploading

raadwiptec — Mon, 04 Jul 2016 06:14:43 GMT

There is a small difference in the actual file .. in terms of header lines and etc... i have sent you the sample..previous and try to adapt...but the actual file has difficulties

Re: uploading

raadwiptec — Mon, 04 Jul 2016 06:15:11 GMT

Hi Remi

There is a small difference in the actual file .. in terms of header lines and etc... i have sent you the sample..previously and try to adapt...but the actual file has difficulties

Re: uploading

raadwiptec — Mon, 04 Jul 2016 07:36:34 GMT

hi remi,

I have attached the file. can you please check and let me know .. i tried changing the header lines in the script to 4..but it gives me strange results

Re: uploading

raadwiptec — Mon, 04 Jul 2016 08:25:45 GMT

hi remi,

if you provide me your email id it would be great.. i can send you the non edited file.. as due to data privacy i do not prefer to share the file in forum..

let me know thanks

Re: uploading

effinty2112 — Mon, 04 Jul 2016 08:36:10 GMT

Hello,

Try this

CrossData:

LOAD *

FROM

Book1.xlsx

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

Transpose(),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

RENAME Field Month to Qtr;

RENAME Field May to Month;

RENAME Field F3 to ValueField;

Data:

CrossTable (Printer,Value,4) LOAD * Resident CrossData;

Drop Table CrossData;

Cheers

Andrew

Re: uploading

raadwiptec — Mon, 04 Jul 2016 09:52:04 GMT

hi andrew

can i send the actual file to ur email..again i have some header issues similiar to remi...?But even your code is giving good results...

Re: uploading

remi_roland — Mon, 04 Jul 2016 10:00:50 GMT

Hi,

I updated the code to adapt it to your new xlsm file.

The main difference beetween the first and the second one were the extra-header line and extra hidden column before and above the table.

Hope it willl be good for you now.

P.S: I also added the year in filter.

Regards.

Remi.

Re: uploading

effinty2112 — Mon, 04 Jul 2016 10:46:12 GMT

Hello,

Yes send me the file, you'll get my e-mail address from my profile.

cheers

Andrew

Re: uploading

raadwiptec — Mon, 04 Jul 2016 12:22:58 GMT

hi andrew.. i dont find ur mail address over here?

Re: uploading

raadwiptec — Mon, 04 Jul 2016 12:37:45 GMT

hi remi,

Thanks.. but sorry when i link to the my actual file... all data gets criss crossed. and data goes to type.. etc like that.....and does not come to as expected...

Probably i need to provide you the orginal file....but as mentioned in the forum it is difficult as due to the data privacy...

Re: uploading

raadwiptec — Mon, 04 Jul 2016 19:10:59 GMT

Hi Remi

Can I send you the orginal file to your mail id? Please confirm

Re: uploading

remi_roland — Tue, 05 Jul 2016 06:48:48 GMT

Hi Raadwiptec,

It's still not good for your excel sheet?

Sure you can send it to me,I will have a look at it to adapt that script to your data format.

Please send me also an exemple of the output format you want if what I did in qvw does't suit.

Regards.

Remi.