topic Re: Regarding Transforming Excel Data in QlikView

Regarding Transforming Excel Data

Thu, 23 Feb 2017 03:03:14 GMT

Hello Friends,

I appreciate your time. I am looking for some transformation step while loading my excel file. Kindly have a look at my attached excel file and I am looking for a data model as attached text file. Kindly could anyone help me regarding this issue ASAP.

Thank you again!

Best regards,

Kiruthiga

Re: Regarding Transforming Excel Data

john9inno — Thu, 23 Feb 2017 03:23:27 GMT

Hi~!

Try below

Directory;

LOAD @1 as Phase,

@2 as Result,

@3 as Value1,

@4 as Value2

FROM

test.xlsx

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

Rotate(left),

Replace(1, bottom, StrCnd(null))

));

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 04:01:12 GMT

Hello John,

I appreciate your help.Thank you

I do have another query,how to eliminate some columns(eg col2,col3..col10)while loading excel through transformation?Could you kindly let met know?

Thank you again

Regards,

Kiruthiga

Re: Regarding Transforming Excel Data

john9inno — Thu, 23 Feb 2017 04:05:57 GMT

add below in the load script

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

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 10))

.......etc

thanks.

John

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 04:24:38 GMT

Thanks so much John; I sincerely appreciate your time and help!

Best regards,

Kiruthiga

Re: Regarding Transforming Excel Data

john9inno — Thu, 23 Feb 2017 04:27:34 GMT

happy to hear that it was helpful.

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 04:47:11 GMT

Hello John,

My pleasure and again started

I have 100's of column to delete. Is there any way we could try using column range or some other function to delete columns while loading?

Thanks for your time again and I sincerely appreciate it.

Kiruthiga

Re: Regarding Transforming Excel Data

john9inno — Thu, 23 Feb 2017 06:24:15 GMT

is there any commonality among columns you want to delete?

thus we can input condition to delete selectively.

John

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 06:28:08 GMT

Hello John,

Thanks again for your time again.Actually, I have attached the sample source and data model. Could you kindly have a look and if possible get a script to achieve the data model.

Thank you!

Kiruthiga

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 06:29:52 GMT

Actually, The column I am deleting doesn't have anything in common. I am deleting those because it is not specified in my data model provided

Thank you!

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 06:37:47 GMT

Guess its pretty much similar to the solution you provided in my previous post ,but getting some errors here:(

Re: Regarding Transforming Excel Data

Anil_Babu_Samineni — Thu, 23 Feb 2017 06:52:56 GMT

Don't create two threads. It will difficult to answer for each of your Threads

Check this -- Re: Regarding Transformation in Excel Load

Re: Regarding Transforming Excel Data

Thu, 23 Feb 2017 08:13:05 GMT

Sure,Thank you Anil!