LOAD DATA FROM UNUSUAL EXCEL FORMAT

[anuradhaa]

I have a excel sheet, but the data format is unusual.

Is it possible to load data from this type of sheet.

Thank You,

[senpradip007]

Where is the DATE field in the attachment ?

[anuradhaa]

it don't have date feild. it's a summary sheet and i like to know if it is possible to load this type of fromats.

Thanks

[MayilVahanan · Accepted solution]

Hi

Try like this

Test:

LOAD If(IsNull(Product), Peek('Product'), Product) as Product,Type, Value1, Value2;

LOAD A as Type,

  If(wildmatch(A,'*Prod*'), A) as Product,

     B as Value1,

     C as Value2

FROM

[..\QV Samples\test1.xlsx]

(ooxml, no labels, table is Sheet1) Where not IsNull(A) ;

Final:

NoConcatenate

LOAD * Resident Test where Value1 > 0;

DROP Table Test;

Result:

Product	Type	Value1	Value2
@Product1	bug	589.7	25.60%
@Product1	chore	129.9	5.60%
@Product1	feature	842.4	36.60%
@Product1	without type	740.92	32.20%
@Product2	bug	49.25	6.10%
@Product2	feature	412.15	51.10%
@Product2	without type	345.45	42.80%
@Product3	bug	123.17	19.80%
@Product3	chore	9.07	1.50%
@Product3	feature	62.22	10.00%
@Product3	without type	427.87	68.80%

[qlikviewwizard]

Hi MayilVahanan

How to load the data without set the xls file to proper format?

Kindly advise.

Thank You.

[sorrakis01]

Hi Mayil,

Firtsly Good work. Only a question: can you explain me why use If(wildmatch(A,'*Prod*'), A) as Product?

what's the difference to use for example If(Left(A,1) = '@') as Product.

Thanks.

[MayilVahanan]

Hi Jordi,

We can also use Left function as mentioned by you.

[sorrakis01]

Thanks Mayil,

then this time it is a matter of tastes

Thanks

[MayilVahanan]

Hi wizard,

How to load the data without set the xls file to proper format?

    We need to analysis from top to bottom and find out the way to import the data in qlikview. Logic may vary based on the xls data format. In some cases, is difficult to import the data in qlikview. In tat case, we need to request the user to give the proper format (or some manual work is there).

[MayilVahanan]

Yes Jordi