topic Re: Regarding Transformation in Excel Load in QlikView

Regarding Transformation in Excel Load

Thu, 23 Feb 2017 05:17:06 GMT

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

Re: Regarding Transformation in Excel Load

Anil_Babu_Samineni — Thu, 23 Feb 2017 05:24:12 GMT

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))

));

Re: Regarding Transformation in Excel Load

Thu, 23 Feb 2017 05:39:23 GMT

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

Re: Regarding Transformation in Excel Load

Anil_Babu_Samineni — Thu, 23 Feb 2017 05:47:52 GMT

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)

)

Re: Regarding Transformation in Excel Load

Thu, 23 Feb 2017 06:05:30 GMT

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

Re: Regarding Transformation in Excel Load

PrashantSangle — Thu, 23 Feb 2017 06:06:50 GMT

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,

Re: Regarding Transformation in Excel Load

Thu, 23 Feb 2017 06:09:11 GMT

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

Re: Regarding Transformation in Excel Load

PrashantSangle — Thu, 23 Feb 2017 06:19:20 GMT

ohh this is pivot table.

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

Regards,

Prashant

Re: Regarding Transformation in Excel Load

Thu, 23 Feb 2017 06:22:38 GMT

Hello Prashant,

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

Kiruthiga

Re: Regarding Transformation in Excel Load

Anil_Babu_Samineni — Thu, 23 Feb 2017 06:50:36 GMT

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)

Re: Regarding Transformation in Excel Load

Thu, 23 Feb 2017 08:13:56 GMT

Thank you guys ,I really appreciate your time and help!

Kiruthiga