topic Re: Cross table in QlikView

Cross table

qlikuser222 — Tue, 12 Apr 2016 19:28:02 GMT

Hi,

I have a requirement which needs data transformation i.e cross table.

Can anyone please let me know how to do it?

load * inline [
Key, name, age
1, John, 12
1, Sam, 13
1, Mike,14
2, John, 15
2, Sam, 16
2, Mike, 17
3, John, 18
3, Sam, 19
3, Mike, 20];

/*
out put should be

Key John Sam Mike
1 12 13 14
2 15 16 17
3 18 19 20

Re: Cross table

sunny_talwar — Tue, 12 Apr 2016 20:17:31 GMT

Try this:

Generic

LOAD * Inline [

Key, name, age

1, John, 12

1, Sam, 13

1, Mike,14

2, John, 15

2, Sam, 16

2, Mike, 17

3, John, 18

3, Sam, 19

3, Mike, 20];

Re: Cross table

qlikuser222 — Tue, 12 Apr 2016 20:33:39 GMT

Thanks for the reply Sunny, but real data has 50 distinct names, which gives us 50 extra tables in the schema.

is there any way to avoid it?

Re: Cross table

sunny_talwar — Wed, 13 Apr 2016 00:20:49 GMT

Look at another thread from today which you might find useful -> Re: Any alternate to Generic load?

But just so you know that different table created by Generic load isn't a bad thing as pointed by HIC here -> The Generic Load‌ and there are ways to make it to concatenate all the individual tables into one big fact table.

Another option discussed can be found here -> Re: Dynamically Naming a Field in Load

Re: Cross table

marcus_sommer — Wed, 13 Apr 2016 07:33:55 GMT

You could join these tables after the generic load, see: Generic Load Joining

- Marcus