topic Re: How do i load these excel files in QlikView

How do i load these excel files

Thu, 18 Feb 2016 14:29:38 GMT

I need a good model to load these 3 sheets in attached excel so I can see the relationship between these 3 tables. I wanna drill down from Description >> Class in Dimension and have Gross, Net and Cost in expression what do I do?

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:09:07 GMT

Hi,

Try This...

Class:
LOAD [S/N] as KEY,
[S/N] AS SN_Class,
CLASS,
[Class no],
GROSS,
NET
FROM

(ooxml, embedded labels, table is Classes);

HostelInfo:
LOAD [S/N] as KEY,
[S/N] AS SN_HostelInfo,
HOSTEL,
Descrition,
[DT number],
Cost
FROM

(ooxml, embedded labels, table is [Hostel info]);

left join (HostelInfo)
LOAD [DT number],
Descrition
FROM

(ooxml, embedded labels, table is Link);

-Sathish

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:09:40 GMT

Re: How do i load these excel files

Anonymous — Thu, 18 Feb 2016 17:09:48 GMT

This way you help?

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:10:09 GMT

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:11:25 GMT

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:11:53 GMT

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:12:21 GMT

Re: How do i load these excel files

Thu, 18 Feb 2016 17:18:49 GMT

S/N cant be our unique identifier. In this case please. There is no [S/N] in the database we just used it to show the numbering

Re: How do i load these excel files

satishkurra — Thu, 18 Feb 2016 17:22:10 GMT

One quick suggestion, Instead of Left Join we can also use ApplyMap and get the Description based on DT Number

Good Explanation Sathish G

I attached the QV application with Apply Map code and the pivot table on UI with same result what Sathish G provided

Apology if any mistakes.

Thanks

Satish

Re: How do i load these excel files

Thu, 18 Feb 2016 17:26:12 GMT

S/N cant be our unique identifier. In this case please.There is no [S/N] in the database we just used it to show the numbering

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:38:13 GMT

OK.. that is the case ClassNo is the key field

Class:
LOAD
// [S/N] as KEY,
[S/N],
CLASS,
[Class no],
[Class no] as KEY,
GROSS,
NET
FROM

(ooxml, embedded labels, table is Classes);

HostelInfo:
LOAD
// [S/N] as KEY,
[S/N] AS SN_HostelInfo,
HOSTEL,
Descrition,
[DT number],
Cost
FROM

(ooxml, embedded labels, table is [Hostel info]);

left join (HostelInfo)
LOAD [DT number],
Descrition as KEY
FROM

(ooxml, embedded labels, table is Link);

-Sathish

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:38:35 GMT

Re: How do i load these excel files

sathishkumar_go — Thu, 18 Feb 2016 17:39:42 GMT

Hi..

You can't use the apply map. Because 1 DT have many Descrition values.

-Sathish