topic Re: Is that possible to change unstructured data into structured data in Qlikview..?? in QlikView

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

Silambarasan1306 — Mon, 15 Feb 2016 05:30:12 GMT

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

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

satishkurra — Mon, 15 Feb 2016 16:03:55 GMT

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

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

rubenmarin — Mon, 15 Feb 2016 16:06:07 GMT

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;

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

Silambarasan1306 — Tue, 16 Feb 2016 04:47:02 GMT

Thank u

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

rubenmarin — Wed, 17 Feb 2016 07:48:54 GMT

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;

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

MarcoWedel — Thu, 18 Feb 2016 00:02:08 GMT

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

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

rubenmarin — Thu, 18 Feb 2016 07:56:22 GMT

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.

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

Silambarasan1306 — Thu, 18 Feb 2016 10:47:14 GMT

I got the result. Thanks for eveyone.

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

MarcoWedel — Thu, 18 Feb 2016 18:36:50 GMT

good to hear,

please close your thread then.

thanks

regards

Marco