topic Re: Data Transformation in Excel File/ filters functions in QlikView

Data Transformation in Excel File/ filters functions

Mon, 07 Apr 2014 12:48:37 GMT

Hi Everyone,

I'm New in QlikView and I have a big problem with data transformation, I have an Excel file which looks like that:

I can't change my source file in Excel (btw. it is possible with basic IF([Machine Type(A2)]='WDFM';[Product Description(C2);[New Column called Category(e3)]) . So I must ask you for your help.

How should I do it in File Wizard or script? I tried to do it by Rotate function and in the script but that didn't work.

And my last question is where to find more information about filters functions to operate on columns and rows in script?

Thank you in advance

Ewelina

Re: Data Transformation in Excel File/ filters functions

peschu123 — Mon, 07 Apr 2014 13:13:58 GMT

Hi Ewelina,

pls take a look at attached QVW.

It is easier if you could provide some sample files...

But I hope this will help you.

Best Regards,

Peter

Re: Data Transformation in Excel File/ filters functions

ariel_klien — Mon, 07 Apr 2014 13:17:08 GMT

Hi,

try to do this:

LOAD [Machine Type],

[Another Data],

[Product Description],

'Category: '&mid(Replace([Product Description],'Product ','Products '),index([Product Description],'Product'),10) as Category,

[Another Data]

from Excel...;

Ariel

Re: Data Transformation in Excel File/ filters functions

Anonymous — Mon, 07 Apr 2014 13:17:27 GMT

Hi Ewelina,

I don't see a need for transformation here - looks like you can define Category based on Product Description:

LOAD
...
if("Machine Type"= 'WFDM', "Product Description", // keep as is
'Category: ' & left("Product Description",9)) as Category
...

Regards,
Michael

Re: Data Transformation in Excel File/ filters functions

Tue, 08 Apr 2014 08:48:26 GMT

Hi Guys,

unfortunately, at the moment I have only Personal Edition, so I can't open .qvw file. I see that I don't explain well my problem. I don't have any keys in description like "Product A" and Categories: Products ABC".

Enclosed you will find one of my source files and final file (which I want to have) with my comments.

I have many files to clean them first and use as generator to aplication.

If I have categories above the rest of products in description column, I do it like that:

filters(

ColXtr(5, RowCnd(CellValue, 6, StrCnd(null)), 0), // column 5 is my description, the next one is empty when in 5 is my category

Replace(28, top, StrCnd(null)),

Top(28, 'Category')

But now I have problem when categories are below their products.

Could you help me with my problem and suggest where to find information about advanced cleaning data in QV (something more than in the books, filters, functions, etc.)?

Best regards

Ewelina

Re: Data Transformation in Excel File/ filters functions

ariel_klien — Tue, 08 Apr 2014 14:37:54 GMT

Hi,

try to do this:

// in this step we load al data from the excel and we give each row a number

Step1:

LOAD RowNo() as RN,

[Machine Type],

[Model Type],

[Ordering PN],

Description,

[Project Name],

[Europe Date],

[Europe Date1],

[Europe Date2],

[Europe Date21],

[Europe Date3],

[Form Factor],

Comments

FROM C:\Users\arielkl\Downloads\Data.xlsx (ooxml, embedded labels, table is [source file]);

// in step 2 we organize Descending numbers and take the previous data to the category.

Step2:

LOAD [Machine Type],

[Model Type],

[Ordering PN],

Description,

if(Previous([Machine Type])='WDFM Model',Previous(Description),Peek('Category')) as Category,

[Project Name],

[Europe Date],

[Europe Date1],

[Europe Date2],

[Europe Date21],

[Europe Date3],

[Form Factor],

Comments

Resident Step1

Order by RN desc;

DROP Table Step1;

NoConcatenate

// final Step we take all the rows beside the categories rows

Final_Table:

LOAD *

Resident Step2

where [Machine Type]<>'WDFM Model';

drop Table Step2;

hope this will help you.

Ariel

Re: Re: Data Transformation in Excel File/ filters functions

Thu, 10 Apr 2014 09:33:22 GMT

Thank you very much Ariel for your helpful answer I,ve already solved my problem with basic filters:

filters(

ColXtr(5, RowCnd(CellValue, 4, StrCnd(null)), 0),

Replace(15, bottom, StrCnd(null)),

Top(15, 'Category'))

but I think that your solution is better. I know the better practise is using script than filters, but I really want to know more (than in the books) about filters. Do you know maybe, where to find that information? For example: Is there more filters than is used in File Wizard, could I operate on Cell Index in Replace function, list of filters and an alternative script functions etc.

Thank you one more time.

Best regards

Ewelina

Re: Data Transformation in Excel File/ filters functions

Thu, 10 Apr 2014 09:50:46 GMT

Hi Michael,

thank you very much for your response. Unfortunately is not what I want in this case,

but thank you for your time.

Best regards

Ewelina