Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a excel sheet, but the data format is unusual.
Is it possible to load data from this type of sheet.
Thank You,
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% |
Where is the DATE field in the attachment ?
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
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% |
Hi MayilVahanan
How to load the data without set the xls file to proper format?
Kindly advise.
Thank You.
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.
Hi Jordi,
We can also use Left function as mentioned by you.
Thanks Mayil,
then this time it is a matter of tastes
Thanks
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).
Yes Jordi