Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Actually I need to work on a excel. Something like that:
Excel:
Year | Information 1 | Information 2 |
---|---|---|
2000 | A1 | B1 |
2000 | A2 | B3 |
2000 | A4 | B2 |
2001 | A6 | B7 |
2002 | A8 | B9 |
2002 | A9 | B10 |
I would like to transform this excel into a QVD like that:
QVD:
Year | Information 1 | Information 2 |
---|---|---|
2000 | A1 | B1 |
2000 | A2 | B3 |
2000 | A4 | B2 |
2001 | A6 | B7 |
2001 | A1 | B1 |
2001 | A2 | B3 |
2001 | A4 | B2 |
2002 | A9 | B10 |
2002 | A6 | B7 |
2002 | A1 | B1 |
2002 | A2 | B3 |
2002 | A4 | B2 |
2002 | A8 | B9 |
"Information 1" and "Information 2" are random values.
Regards
try this,
Fact:
LOAD Year,
concat( DISTINCT [Information 1]&'-'& [Information 2],',') as Info
FROM
[https://community.qlik.com/thread/186210?sr=stream]
(html, codepage is 1252, embedded labels, table is @1)
where len(trim(Year))>0
Group by Year;
New:
noconcatenate
LOAD Distinct *,
if(RowNo()=1,Info,
if(Year<>Previous(Year),Info &','& Peek('Info1'))) as Info1
Resident Fact
Order by Year ;
DROP Table Fact;
Final:
LOAD Distinct Year,
SubField(Info2,'-',1) as Information1,
SubField(Info2,'-',2) as Information2;
LOAD Year,
SubField(Info1,',') as Info2
Resident New;
DROP Table New;
for 2002 why you are not considering A8?
Exactly. I am not seeing a pattern.
Sorry I forgot A8. The specificity of this table is that I need to preserver the last datas and put them in current year.
I might be missing something but it looks like you are just in need of an outer join of the two fields. That would give you all of the values from column one and two. Sorry I might need to see the corrected table again but that's what its looking like,
Outter join it's not for 2 tables? Because the first table is a file from an excel.
that doesn't matter. You can load the one table twice and then do an outer join.
example:
load distinct
year
from ExcelFile;
outer join
load distinct
information 1
from ExcelFile;
left join
// this is if you need the other column
load distinct
information 1,
information 2
from ExcelFile;
store example into exampleQVD.qvd (qvd);
Effectively, I have something looks like what I need, but there's still a issue. In my second table, 2000 doesn't take 2001 results. I'm trying to be simple, first year I have some results = > Second year, I need to get the first year results plus the second year => Same thing for the next: Need: First and second years plus third.
can you repost the example table again but with the correction that you mentioned.
Excel:
Year | Information 1 | Information 2 |
---|---|---|
2000 | A1 | B1 |
2000 | A2 | B3 |
2000 | A4 | B2 |
2001 | A6 | B7 |
2002 | A8 | B9 |
2002 | A9 | B10 |
QVD
Year | Information 1 | Information 2 |
---|---|---|
2000 | A1 | B1 |
2000 | A2 | B3 |
2000 | A4 | B2 |
2001 | A6 | B7 |
2001 | A1 | B1 |
2001 | A2 | B3 |
2001 | A4 | B2 |
2002 | A9 | B10 |
2002 | A6 | B7 |
2002 | A1 | B1 |
2002 | A2 | B3 |
2002 | A4 | B2 |
2002 | A8 | B9 |
In Red data from 2000
In Blue data from 2001
In Puple data from 2002