topic Re: Need Help in data Extract in App Development

Need Help in data Extract

kicchu465 — Sat, 16 Nov 2024 21:00:13 GMT

Hi Experts,

I have a requirement for extracting data in Qlik Sense.

My data file Contains as below

Field1	Field2	Field3	Field4	Field5	Field6	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19
A	A1	A2	A3	A4	A5	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120	121
B	B1	B2	B3	B4	B5	200	201	202	203	204	205	206	207	208	209	210	211	212	213	214	215	216	217	218	219	220	221
C	C1	C2	C3	C4	C5	300	301	302	303	304	305	306	307	308	309	310	311	312	313	314	315	316	317	318	319	320	321

The data gets added to data file month on Month, So My requirement is to extract Only the Latest Month Instead of Extracting all the months data.

My Output Should look like as below

Field1	Field2	Field3	Field4	Field5	Field6	Jan-19
A	A1	A2	A3	A4	A5	121
B	B1	B2	B3	B4	B5	221
C	C1	C2	C3	C4	C5	321

I Cannot use Addmonths function because I am not sure when the Data gets updated in the data file, So the data extraction should be dynamic.

If you can share your thoughts it would be very helpful.

Thanks,

Kishore

Re: Need Help in data Extract

sunny_talwar — Tue, 23 Apr 2019 19:38:12 GMT

One way to do this

Table:
CrossTable (MonthYear, Data, 6)
LOAD Field1, 
     Field2, 
     Field3, 
     Field4, 
     Field5, 
     Field6, 
     [42826], 
     [42856], 
     [42887], 
     [42917], 
     [42948], 
     [42979], 
     [43009], 
     [43040], 
     [43070], 
     [43101], 
     [43132], 
     [43160], 
     [43191], 
     [43221], 
     [43252], 
     [43282], 
     [43313], 
     [43344], 
     [43374], 
     [43405], 
     [43435], 
     [43466]
FROM
[Need Help in data Extract.xlsx]
(ooxml, embedded labels, table is Sheet1);

FinalTable:
NoConcatenate
LOAD Field1,
	 Field2,
	 Field3,
	 Field4,
	 Field5,
	 Field6,
	 Date(Num#(MonthYear), 'MMM-YY') as MonthYear,
	 Data
Resident Table;

DROP Table Table;

Right Join (FinalTable)
LOAD Max(MonthYear) as MonthYear
Resident FinalTable;

Wrote the script in QlikView, but should work the same way in Sense

Re: Need Help in data Extract

kicchu465 — Wed, 24 Apr 2019 04:22:51 GMT

@sunny_talwar

Thanks very muvh sunny for quick reply.

I am trying to do incremental Load here.

In the first run the app should load all months data and from the next run it should pick only the latest month instead of reading all the months and concatenate with existing qvd.

I tried doing preceding but cant get the logic right.

Thanks

S k

Re: Need Help in data Extract

jonathandienst — Wed, 24 Apr 2019 06:15:35 GMT

Perhaps like this:

Incremental:
LOAD Field1,
	Field2,
	Field3,
	Field4,
	Field5,
	Field6,
	Month,
	Month as Loaded,
	Value
From Incremental.qvd

T_Fields:
CrossTable(Month, Value, 6)
LOAD * 
From ...;

Concatenate(Incremental)
LOAD Field1,
	Field2,
	Field3,
	Field4,
	Field5,
	Field6,
	Month,
	Value
Resident T_Fields
Where Not(Exists(Loaded, Month));

DROP Table T_Fields;

You can't do a preceding load on top of a cross table load, so you have to do a load resident instead.