Data Load in QlikView

[sona_sa]

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

[#2]

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.

Or

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.

[jagan]

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.

[sona_sa]

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.

[MayilVahanan]

hi

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;

[jagan · Accepted solution]

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.

[Kushal_Chawda]

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;

[ankitbisht01]

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