topic Load all rows in script from a file in QlikView

Load all rows in script from a file

razvan_brais — Wed, 25 Nov 2020 16:16:04 GMT

Hy guys. I would like to load all rows from a file even if there is no associations between table.

I`m having three tables.

Table1 with ID , Date , Value

Table2 with ID , Level1 , Level2,

Table3 with Level1 , Level2,Budget.

When I`m using the normal load it will only load all fields that have a match between tables.

Such as:

ID	Date	Value
1	06/01/2016	100
2	07/01/2016	50
3	08/01/2016	150

ID	LEVEL1	LEVEL2
1	Electronics	Laptops
2	Electronics	SmartWatches
3	Electronics	SmartPhones
4	Electronics	Gaming

Level1	Level2	Budget
Electronics	Laptops	700
Electronics	SmartWatches	300
Electronics	SmartPhones	400
Electronics	Gaming	200

What I want to do is to show all data in a pivot table even if there is no association between Table1 and Table 2. The result I would like to look something like this :

ID	LEVEl1	LEVEL2	Value	Budget
1	Electronics	Laptops	100	700
2	Electronics	SmartWatches	50	300
3	Electronics	SmartPhones	150	400
4	Electronics	Gaming	0	200

Please help me , because I can`t find a way to do this.

Re: Load all rows in script from a file

Gysbert_Wassenaar — Wed, 05 Oct 2016 08:24:11 GMT

MyTable:

LOAD ID, Date, Value FROM Table1;

JOIN (MyTable)

LOAD ID, LEVEL1, LEVEL2 FROM Table2;

JOIN (MyTable)

LOAD Level1 as LEVEL1, Level2 as LEVEL2, Budget FROM Table3;

Re: Load all rows in script from a file

varshavig12 — Wed, 05 Oct 2016 08:24:53 GMT

Rename the columns

Re: Load all rows in script from a file

Anil_Babu_Samineni — Wed, 05 Oct 2016 08:28:18 GMT

Try

Join(Table Name)

Then use Qualify & Un Qualify for that to get updated Values

Level1 as LEVEL1 , Level2 as LEVEL2 for 3rd Table while resident

Re: Load all rows in script from a file

razvan_brais — Wed, 05 Oct 2016 08:29:32 GMT

I don`t want to rename the columns because I will need the same column name in expressions.

Gysbert . The problem is that my First table will be stored in a qvd file , and I don`t want to save all of the info from those three tables.

Re: Load all rows in script from a file

razvan_brais — Wed, 05 Oct 2016 08:29:59 GMT

Can you pls attach the qvw file?

Re: Load all rows in script from a file

Gysbert_Wassenaar — Wed, 05 Oct 2016 08:34:22 GMT

If you don't want to rename fields then you will end up with a monster that will eat all your ram and cpu cycles and molest your kittens. Don't try to make Qlikview do things it shouldn't do.

If you don't want to store the resulting joined table in a qvd then simply don't store it in a qvd. Nobody's forcing you to do so.

Re: Load all rows in script from a file

Anil_Babu_Samineni — Wed, 05 Oct 2016 08:38:21 GMT

Try This, For ref

Re: Load all rows in script from a file

razvan_brais — Wed, 05 Oct 2016 08:57:38 GMT

You are saying that if I have the same column name it will put in my table all of the data?

Re: Load all rows in script from a file

razvan_brais — Wed, 05 Oct 2016 09:20:04 GMT

Anil , it doesn`t work. I`m trying to show all data in a pivot table. But I have no success in doing that.

Re: Load all rows in script from a file

Anil_Babu_Samineni — Wed, 05 Oct 2016 09:22:03 GMT

Would you able to provide sample and expected o/p

Re: Load all rows in script from a file

jyothish8807 — Wed, 05 Oct 2016 09:43:17 GMT

Hi Razvan,

Try this:

T1:

LOAD * Inline [

ID, Date, Value

1, 06/01/2016, 100

2, 07/01/2016, 50

3, 08/01/2016, 150];

T2:

LOAD * Inline [

ID, LEVEL1, LEVEL2

1, Electronics, Laptops

2, Electronics, SmartWatches

3, Electronics, SmartPhones

4, Electronics, Gaming

];

T3:

LOAD * Inline [

Level1, Level2, Budget

Electronics, Laptops, 700

Electronics, SmartWatches, 300

Electronics, SmartPhones, 400

Electronics, Gaming, 200];

NoConcatenate

Main_Table:

LOAD * Resident T1;

join

LOAD * Resident T2;

join

LOAD Level1 as LEVEL1, Level2 as LEVEL2,Budget

Resident T3;

Drop table T1,T2,T3;

Regards

Re: Load all rows in script from a file

qlikview979 — Wed, 05 Oct 2016 09:45:20 GMT

Hi ,

Please find the attached file

Pivot is showing Exact Result

Re: Load all rows in script from a file

razvan_brais — Wed, 05 Oct 2016 14:21:49 GMT

I can`t provide the example. My value from level 2 is under level 1.

In my xls file I have data for it , but in my database where I have sums for every category it doesn`t exists.

What I can`t understand is that when I uncheck supress null values from level1 it shows me this info but not under the level where it should be.