topic Re: Data load in QlikView

Data load

Sun, 13 Oct 2013 23:54:06 GMT

Dear Legends,

May I please get a little help on the scenario given below;

Tab1:

ITEMNO	Custs	QTY	Total
1	X	1	130
2	Y	2	50
3	Z	2	50

Tab2: (Same Items assigned to different values as Labels and no sales total in this table)

ITEMNO	Labels
1	A
2	B
3	C

What do I want to achieve is;

Tab3:

AllCusts	QTY	Total
X	1	130
Y	2	50
Z	2	50
A	3	100
B	3	100

I shall appreciate the precious time you could put on it.

Kind Regards,

Aamir

Re: Data load

Mon, 14 Oct 2013 02:59:04 GMT

Can you please paste your load query?

Re: Data load

jagan — Mon, 14 Oct 2013 03:24:24 GMT

Hi,

On what basis the Qty and Total is assigned to A and B, if you just want 100 then simply use this

Tab1:

LOAD

FROM Tab1;

Tab2:

LOAD *

FROM Tab2

Tab3:

NoConcatenate

LOAD

RESIDENT Tab1;

Concatenate(Tab3)

LOAD

3 AS QTY,

100 AS Total

RESIDENT Tab2;

If you dont want Tab1 and Tab2 just drop it by using DROP TABLES Tab1, Tab2;

Hope this helps you.

Regards,

Jagan.

Re: Data load

Mon, 14 Oct 2013 03:35:15 GMT

Hi Anju,

Please find the load query below but I am sure it is not the right way to do it.

TAB1:

LOAD CUSTS,

*ITEM as ITEMNO,

Sale;

SQL CUSTS,

ITEM,

Sale

FROM LH.dbo."TAB1";

Tab2:

LOAD BRAND,

COLOUR,

DESIGN,

*ITEMNO,

LABEL;

SQL SELECT BRAND,

COLOUR,

DESIGN,

ITEMNO,

LABEL,

FROM LH.dbo."tab2";

ADD LOAD ITEMNO as ITEMS,

LABEL as E_LABEL,

if (wildmatch(LABEL, 'EXCO'), 'EXCO',

if (WildMatch(LABEL,'*COMMERCIAL*'),'COMERCIAL','OTHERS')) as *CUSTS;

SQL SELECT ITEMNO,

LABEL

FROM LH.dbo."TAB2";

Re: Data load

Mon, 14 Oct 2013 05:35:05 GMT

Hi,

You can add this under Tab2:

'LABEL AS CUSTS'

Next, for table 3, Create a pivot table with custs as dimension and add the other values under expression.

But, how are you calculating qty and total for customers A,B and C?

Re: Data load

Sat, 01 Mar 2014 20:59:20 GMT

Hi Aamir,

Jagan is right, if you want only 3 as QTY and 100 as Total for Cust A,B, then this will solve your purpose.

ResultTable:

LOAD

ITEMNO,

Custs as AllCusts,

QTY,

Total

FROM Tab1;

CONCATENATE

LOAD

ITEMNO,

Labels as AllCusts,

3 as QTY,

100 as Total

FROM Tab2

Hope this is helpful.

Thanks

Syed Haji