Is that possible to change unstructured data into structured data in Qlikview..??

[Silambarasan1306]

Office	EmployeeID	2008	2009	2010	2011	2012
1	2	2,000	24,000	15,000	10,000	10,000
	9		20,000	50,000	80,000	1,00,000
2	1	5,000	40,000	60,000	80,000	1,00,000
	8	30,000	50,000	80,000	1,00,000
3	4		13,000	50,000	80,000	1,00,000
	5			50,000	80,000	1,00,000
4	6		10,000	50,000	80,000	1,00,000
5	3		40,000	50,000	80,000	1,00,000

[satishkurra]

Use CROSS TABLE LOAD to fill the blank records...

[rubenmarin]

Hi, please check if you're looking for the attached solution, the script is:

Data_tmp:

LOAD If(Isnull(Office), Peek(Office), Office) as Office,

    EmployeeID,

    [2008],

    [2009],

    [2010],

    [2011],

    [2012]

FROM

[.\test.xls]

(biff, embedded labels, table is Hoja1$);

CrossTable(Year,Value, 2)

LOAD * Resident Data_tmp;

DROP Table Data_tmp;

[Silambarasan1306]

Thank u

[rubenmarin]

And to avoid using fixed year column names you can use something like this script:

Data_tmp:

LOAD If(Isnull(Office), Peek(Office), Office) as Office_filled,

     *

FROM [.\test.xls] (biff, embedded labels, table is Hoja1$);

DROP Field Office;

CrossTable(Year,Value, 2)

LOAD * Resident Data_tmp;

DROP Table Data_tmp;

RENAME Field Office_filled to Office;

[MarcoWedel]

Hi,

as crosstable load always interpret column names as text, one solution could also be:

table1:
CrossTable (YearTemp, AmountTemp, 2)
LOAD * FROM [https://community.qlik.com/thread/205505] (html, codepage is 1252, embedded labels, table is @1, filters(Replace(1, top, StrCnd(null))));

Left Join (table1)
LOAD Distinct YearTemp, Num#(YearTemp) as Year Resident table1;

Left Join (table1)
LOAD Distinct AmountTemp, Num#(AmountTemp,'#,##,###') as Amount Resident table1;

DROP Fields YearTemp,AmountTemp;

hope this helps

regards

Marco

[rubenmarin]

You're right Marco, also I want to thank you the elegant solution for the master calendar in this post: Master Calendar‌

I'm using it since I look that post.

[Silambarasan1306]

I got the result. Thanks for eveyone.

[MarcoWedel]

good to hear,

please close your thread then.

thanks

regards

Marco