uploading

[raadwiptec]

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

[raadwiptec]

hi any suggestions here?

[qlikview979]

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

[remi_roland · Accepted solution]

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.

[raadwiptec]

there are multiple headers

[Peter_Cammaert]

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

[raadwiptec]

hi remi,

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

[remi_roland]

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.

[raadwiptec]

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

[raadwiptec]

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