Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner
Partner

LOAD DATA FROM UNUSUAL EXCEL FORMAT

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

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

Thank You,

1 Solution

Accepted Solutions
MayilVahanan

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
@Product1bug589.725.60%
@Product1chore129.95.60%
@Product1feature842.436.60%
@Product1without type740.9232.20%
@Product2bug49.256.10%
@Product2feature412.1551.10%
@Product2without type345.4542.80%
@Product3bug123.1719.80%
@Product3chore9.071.50%
@Product3feature62.2210.00%
@Product3without type427.8768.80%
Thanks & Regards,
Mayil Vahanan R

View solution in original post

12 Replies
senpradip007
Specialist III
Specialist III

Where is the DATE field in the attachment ?

anuradhaa
Partner
Partner
Author

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

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
@Product1bug589.725.60%
@Product1chore129.95.60%
@Product1feature842.436.60%
@Product1without type740.9232.20%
@Product2bug49.256.10%
@Product2feature412.1551.10%
@Product2without type345.4542.80%
@Product3bug123.1719.80%
@Product3chore9.071.50%
@Product3feature62.2210.00%
@Product3without type427.8768.80%
Thanks & Regards,
Mayil Vahanan R

View solution in original post

qlikviewwizard
Master II
Master II

Hi MayilVahanan

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

Kindly advise.

Thank You.

sorrakis01
Specialist
Specialist

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.

Thanks & Regards,
Mayil Vahanan R
sorrakis01
Specialist
Specialist

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

Thanks & Regards,
Mayil Vahanan R
MayilVahanan

Yes Jordi

Thanks & Regards,
Mayil Vahanan R