topic Re: Data transformation - fill 3 or more columns in QlikView

Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 09:10:04 GMT

Hello QV Experts, I have a problem with data transformationinto expected format. My data source looks like that:

And I really need it in that form:

I know, that I can use fill function in wizzard, but for 3 or 4 data levels? Is there possible to do it in script?

Do you have any idea, how to solve this problem?

I will be very grateful.

Ewe

Re: Data transformation - fill 3 or more columns

mdmukramali — Mon, 13 Oct 2014 09:48:38 GMT

Dear,

you can use the if condition in script to load the required data.

can you share with us sample file so that we can help you,

Thanks,

Mukram.

Re: Data transformation - fill 3 or more columns

preminqlik — Mon, 13 Oct 2014 10:06:56 GMT

hi try this

Load *

from path

where len(Date1)>0;

Re: Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 10:19:51 GMT

Hi Mukram,

thank you for fast reply.

Unfortunately, I can't attach an orginal source. So this is just sample data in .txt format.

Re: Re: Re: Data transformation - fill 3 or more columns

mdmukramali — Mon, 13 Oct 2014 10:30:33 GMT

Dear ,

Kindly find the attached file.

Re: Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 10:31:43 GMT

Hi, well I want to have some categories after last Date column (like on last picture). My fist category is marked as violet colour etc. I want to garbage this statement but before that I want to create 3 or 4 levels of categories which are marked above.

Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 10:40:33 GMT

I rally need to transform my data like on this picture:

Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 10:42:33 GMT

Hi try to use below script

Directory;

LOAD PN1,

PN2,

PN3,

Description,

Language,

ID,

US,

[Non-Emea],

EMEA,

Date1,

Date2,

Date3,

Date4

FROM

[..\..\certification prep.xlsx]

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

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 9)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2))

));

{try to use Garbage property with delete marked }

Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 10:55:29 GMT

Hi Poja,

I rather would like to know, how to fill new columns with categories from Description.

Statements from violet cells are my Category1, from blue cells are Category2 etc. so I need them in my final table.

Re: Data transformation - fill 3 or more columns

Mon, 13 Oct 2014 11:03:07 GMT

I did not check full final table before, will try and let u know my findings

Re: Data transformation - fill 3 or more columns

preminqlik — Tue, 14 Oct 2014 05:53:57 GMT

well, upload sample excel here, we can do that by looping after using Peek

hint: first you need to load orginal data as it is then you need to start peeking