Regarding Transformation in Excel Load

[#1]

Dear Friends,

I appreciate your time. Could you kindly let me know the best way to delete a range of columns for example( column 10 to column 95) while loading excel file into QlikView.

I sincerely appreciate your help and time.

Best regards,

Kiruthiga

[Anil_Babu_Samineni]

May be this code

LOAD Column1,

     Column4,

     Column5

FROM

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

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 3))

));

[#3]

Hello Anil,

I appreciate your time. Guess by applying the above solution we need to give a conditional delete  by clicking each column (under Transformation) but is there any other way because I have 100's of column to delete. It would be great if you could you kindly let me know the best way to delete those 100 columns in a single load.

Thank you again!

Best regards,

Kiruthiga

[Anil_Babu_Samineni]

I would say, There is no way to remove all specific columns at a time.

1) Should be

Load * From ;

Drop Field Column2, Column3, Column4;

2) May be this

LOAD Column1,

    Column4,

    Column5

FROM

(ooxml, embedded labels, table is Sheet1,

filters(

rotate(left),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Test'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Test2'))),

rotate(right)

)

[#5]

Hello Anil,

Thank you so much for your time and I sincerely appreciate it. Actually, I have attached the data and my desired data model, if possible could you kindly transform it  for me. I am digging my head for a long time though:(

Thank you again!

Kiruthiga

[PrashantSangle]

Hi,

Instead of removing not neccessary column. You can load only required column.

For example in your script Use

LOAD Column1,Column2 from tableName;

Regards,

[#7]

Hello Prashant,

Thanks for your time. But my requirement is little different. Could you kindly have a look at my attached source and my desired data model and could you kindly guide me the best approach to achieve it .

Thank you again, I appreciate your time.

Kiruthiga

[PrashantSangle]

ohh this is pivot table.

give me some time. I will get back to you.

Regards,

Prashant

[#9]

Hello Prashant,

Thank you so much..,awaiting to see the result:)

Kiruthiga

[Anil_Babu_Samineni · Accepted solution]

I don't think how many fields in real data.

Seems Pivot table this

Data model this

Code i used this to implement in attachment(.Txt File)