topic Re: Data Load in QlikView in QlikView

Data Load in QlikView

sona_sa — Tue, 07 Jul 2015 04:11:19 GMT

Hi I have a xls file now I want to load it in QlikView.

1. Field Name - Team, Year, Budget is Budget amt, P01 - P12 is my Month and below P01 - P12 is my Value for that period.

How we can do that. Please find the attached xls.

Thanks

Re: Data Load in QlikView

Tue, 07 Jul 2015 04:17:50 GMT

Hi,

You may load your data into a QVD file and store it at a location and then next level you may pull data from QVD and do the required calculation.

You may use Resident option to do the calculation at next level.

Even you may directly write a select quires as you required when pulling data from xls.

Let me know If you need sample query.

Re: Data Load in QlikView

jagan — Tue, 07 Jul 2015 04:19:02 GMT

Hi,

Use CrossTable() and load the data by using below script

Data:

CrossTable(Month, Value, 3)

LOAD TEAM,

Year,

BUDGET,

P01,

P02,

P03,

P04,

P05,

P06,

P07,

P08,

P09,

P10,

P11,

P12

FROM

[TEST.xlsx]

(ooxml, embedded labels, table is Sheet1);

Hope it helps you.

Regards,

Jagan.

Re: Data Load in QlikView

sona_sa — Tue, 07 Jul 2015 05:40:54 GMT

I got the output. But for team 880.019 Budget is 3360000.00, But Always Budget Amount is for whole Year 2014 and It would be 280000. Actual Value will be month wise.

I want the Budget for 880.019 should be 3360000.

Thanks.

Re: Data Load in QlikView

MayilVahanan — Tue, 07 Jul 2015 05:50:10 GMT

Try like this

Table1:

CrossTable(Month, Value,4)

LOAD AutoNumberHash128(TEAM&Year&BUDGET) as %Key,

TEAM,

Year,

BUDGET,

P01,

P02,

P03,

P04,

P05,

P06,

P07,

P08,

P09,

P10,

P11,

P12

FROM

(ooxml, embedded labels, table is Sheet1);

BudgetInfo:

LOAD Distinct %Key, BUDGET Resident Table1;

DROP Field BUDGET From Table1;

Re: Data Load in QlikView

jagan — Tue, 07 Jul 2015 05:58:35 GMT

Hi,

Then try like this

Actual:

CrossTable(Month, Value, 3)

LOAD

AutoNumber( TEAM&Year) AS Key,

TEAM,

Year,

P01,

P02,

P03,

P04,

P05,

P06,

P07,

P08,

P09,

P10,

P11,

P12

FROM

[TEST.xlsx]

(ooxml, embedded labels, table is Sheet1);

Budget:

LOAD

AutoNumber( TEAM&Year) AS Key

TEAM,

Year,

BUDGET

FROM

[TEST.xlsx]

(ooxml, embedded labels, table is Sheet1);

Hope this helps you.

Re: Data Load in QlikView

Kushal_Chawda — Tue, 07 Jul 2015 06:12:03 GMT

Budget:

CrossTable(Month, Value, 2)

LOAD TEAM,

Year,

P01,

P02,

P03,

P04,

P05,

P06,

P07,

P08,

P09,

P10,

P11,

P12

FROM

[TEST.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD TEAM,

Year,

sum(BUDGET) as BUDGET

FROM

[TEST.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by TEAM,Year;

Re: Data Load in QlikView

ankitbisht01 — Tue, 07 Jul 2015 10:07:14 GMT

i have tried it with pivot table on front end - PFA